Aggregate and filter values across worksheets

[sottovoce1]

Hi, I’m trying to solve a problem similar to the one in this thread: How to sum up many vlookups? that @JohnSUN provided an answer for.

I’d like to do the following:

1. given a table of items and their values,
2. and an intake table of some subset of those items, their quantity, and a date when they were logged,
3. return an aggregate by date of the sum-product of values and their quantity, for those items listed in the intake form.

Hopefully the attached example explains the goal. My start to a solution (which isn’t working) highlighted in green. Note that intake and values tables are in the same worksheet for convenience but these would be in separate sheets in the actual implementation.

Aggregate_by_item_and_quantity.ods (21.3 KB)

[erAck]

What the formula in C29 does can be investigated using the Function Wizard (Ctrl+F2), or by splitting it up into the components fed to SUMPRODUCT() and entered as array formulas, which yields:

OFFSET	$C15:$C22	COUNTIF	*
33	1	1	33
22		0	0
4	2	0	0
1	0.5	0	0
	0	0	0
5	0	0	0
3	0	0	0
1	0	2	0

As you can see the order of array elements does not match what you would need.
That can be accomplished by

=SUMPRODUCT(
IFNA(OFFSET(Names;MATCH(ListOfItems;Names;0)-1;MATCH($B29;Values;0);1;1);0);
OFFSET(ListOfItems;0;1))

Note that the named range ListOfItems is defined to $Sheet1.$B$15:$B$19 so the array of quantity values must have the same length, hence OFFSET(ListOfItems;0;1) and not $C15:$C22.

For SUMPRODUCT that’s then

Array1	Array2	*
1	1	1
0		0
33	2	66
1	0.5	0.5
0	0	0

You can step through the formula in the Function Wizard to see the interim arrays of the sub-expressions.

[sottovoce1]

@erAck, Very much appreciate the complete answer and your solution did the trick. Using array formulas was very helpful and the Function Wizard also shed some light – much better in Libre Office than in Open Office but still wrapping my head around the formula. One note is that Libre Office on my Mac (2021, Monterrey) keeps crashing when I have two workbooks open so that’s been a challenge but a separate topic.

I now want to constrain/ filter the sumproduct of values and quanties by the date of input. I’ve uploaded another spreadsheet with the desired output. Could you give a hint of how that can be achieved?

Aggregate_by_item_and_quantity_Jun_5_22.ods (17.6 KB)

[erAck]

You already have a solution: the pivot table.

[sottovoce1]

@erAck, The pivot table requires a third column by date but I didn’t see how to pull that. So I want to group by date in order to use a pivot on that data. Currently, there is this:

items	value * quantity
val1	64
val2	90
val3	18.5
val4	20.5

But this is what I want:

items	value * quantity	by date
val1	28	05/31/22
val2	30	05/31/22
val3	15.5	05/31/22
val4	3	06/05/22

Any ideas?

[sokol92]

Your pivot table properties:
Row Fields: Items, by date
Column Fields: None
Data Fields: Sum - value * quantity
Aggregate_by_item_and_quantity_Jun_5_22.ods (27.1 KB)

[sottovoce1]

To simplify the question, lets forget about the pivot table.

I just want this aggregated output by date:

items	value * quantity	by date
val1	28	05/31/22
val2	30	05/31/22
val3	15.5	05/31/22
val4	3	06/05/22

Currently, there is no group by date happening. Is it possible to add that given the existing data in the spreadsheet?

The new attachment shows where things stand now and the desired behavior
Aggregate_by_item_and_quantity_Jun_6_22.ods (16.5 KB)
.