Can XLookUp sum also?

[Jazz Engineer]

I purchased a Kindle copy of Bill Jelen's Excel 2021 Inside Out. Chapter 9 discusses how, thanks to Dynamic Arrays, a single XLookUp formula can return multiple values. The example used on page 229 is what I want to discuss. At first this seems like a cool idea. But that's because the Inventory By Month range is not sorted by Item ID. If you sort the range on that field, you'll find that, in many instances, there is more than one occurrence of an ID. It seems to me that the destination range, A4:N12, is intended to sum the monthly inventory of each item ID. This formula does NOT do that.

My question is: Could a single formula be written that locates all occurrences of each Item ID and sums the inventory values per month.

The formula looks like this: =XLOOKUP(B6,$B$19:$B$14058,$C$19:$N$14058).

If you assign names to the ranges, it looks like this: =XLOOKUP(B5,InventoryID,MonthlyInventory)

My PC kept hanging when I installed XL2BB. When I tried SnagIt, the file, though only about 15 rows plus the formula bar, was too big.

Thanks,

Steve / JazzEngineer

 

[etaf]

what about
=SUMPRODUCT(($B$19:$B$14058=B6)*($C$19:$N$14058))

Book7
	A	B	C	D	E	F
1
2
3					35
4
5
6		a
7
8
9
10
11
12
13
14
15
16
17
18
19		a	2		12
20
21
22		a		6
23
24
25		a		1	14
26
Sheet1

Cell Formulas
Range	Formula
E3	E3	=SUMPRODUCT((B19:B27=B6)*(C19:E27))

 

[Jazz Engineer]

what about
=SUMPRODUCT(($B$19:$B$14058=B6)*($C$19:$N$14058))

Book7
	A	B	C	D	E	F
1
2
3					35
4
5
6		a
7
8
9
10
11
12
13
14
15
16
17
18
19		a	2		12
20
21
22		a		6
23
24
25		a		1	14
26
Sheet1

Cell Formulas
Range	Formula
E3	E3	=SUMPRODUCT((B19:B27=B6)*(C19:E27))

That seems like it might work. I'll have to study SumProduct to understand it and work out a solution in the original data file. But I guess that, as long as I had a single column of unique Item IDs, each row's formula would adjust and calculate the results. I'll get back to you.

Steve

 

[Alex Blakenburg]

Or alternatively

=SUM(FILTER($C$19:$N$14058,$B$19:$B$14058=$B$6))

 

[Jazz Engineer]

That seems like it might work. I'll have to study SumProduct to understand it and work out a solution in the original data file. But I guess that, as long as I had a single column of unique Item IDs, each row's formula would adjust and calculate the results. I'll get back to you.

Steve

Etaf,

Both of your formulae worked! Thanks.

 

[Jazz Engineer]

Or alternatively

=SUM(FILTER($C$19:$N$14058,$B$19:$B$14058=$B$6))

Alex,

Your solution also worked.

Steve

 

[etaf]

you are welcome