SUM XLOOKUP

[volsfan210000]

Hi -

Have the current SUM XLOOKUP formula

=SUM(XLOOKUP(D$6,'Spend Plan Setup '!$L$5:$BC$5,'Spend Plan Setup '!$L$10:$BC$117):XLOOKUP($C8,'Spend Plan Setup '!$L$6:$BC$6,'Spend Plan Setup '!$L$10:$BC$117))

D$6 represents the FY
$C8 represents the month

For the 1st month it is returning the correct value but when I take the formula to second month it is giving a cumulative answer not the sum of the month I'm looking for.

Month	FY25
Oct	$64,570
Nov	$141,518
Dec	$211,345
Jan	$288,293
Feb	$358,120
Mar	$427,947
Apr	$499,999
May	$570,271
Jun	$640,543
Jul	$724,410
Aug	$810,502
Sep	$867,951
Grand Totals	$5,605,467​

 

[PeteWright]

It seems difficult to answer without knowing your other data. Could you provide a screenshot of all relevant cells?

 

[StephenCrump]

It seems difficult to answer without knowing your other data ....

... but let's have a guess anyway.

@volsfan210000, I think you have the wrong formula construction. Your first XLOOKUP will return the first column of data where year = FY25. And the second XLOOKUP wil return the first column of data where month = Oct. So you'll be summing across multiple columns, unless you get lucky and both columns align.

Does this work for you?

=SUM(XLOOKUP(1,('Spend Plan Setup '!$L$5:$BC$5=D$6)*('Spend Plan Setup '!$L$6:$BC$6=$C8),'Spend Plan Setup '!$L$10:$BC$117))

Or if you have Excel 365 or 2021: =SUM(FILTER('Spend Plan Setup '!$L$10:$BC$117,('Spend Plan Setup '!$L$5:$BC$5=D$6)*('Spend Plan Setup '!$L$6:$BC$6=$C8)))

 

[volsfan210000]

... but let's have a guess anyway.

@volsfan210000, I think you have the wrong formula construction. Your first XLOOKUP will return the first column of data where year = FY25. And the second XLOOKUP wil return the first column of data where month = Oct. So you'll be summing across multiple columns, unless you get lucky and both columns align.

Does this work for you?

=SUM(XLOOKUP(1,('Spend Plan Setup '!$L$5:$BC$5=D$6)*('Spend Plan Setup '!$L$6:$BC$6=$C8),'Spend Plan Setup '!$L$10:$BC$117))

Or if you have Excel 365 or 2021: =SUM(FILTER('Spend Plan Setup '!$L$10:$BC$117,('Spend Plan Setup '!$L$5:$BC$5=D$6)*('Spend Plan Setup '!$L$6:$BC$6=$C8)))

Thanks!!!

@StephenCrump both of these worked perfectly!!

one question! On the =SUM(XLOOKUP(1. What does the (1 represent

 

[StephenCrump]

1 is the product of two boolean TRUE values. Here's a simple example to illustrate:

	A	B	C	D	E	F	G	H	I
1				FY24	FY24	FY24	FY25	FY25	FY25
2				Jul	Aug	Sep	Oct	Nov	Dec
3				1	2	3	4	5	6
4	Find	FY25		7	8	9	10	11	12
5		Nov		13	14	15	16	17	18
6	Result	33
7
8	In steps ...
9	Match year			FALSE	FALSE	FALSE	TRUE	TRUE	TRUE
10	Match month			FALSE	FALSE	FALSE	FALSE	TRUE	FALSE
11	Match both			0	0	0	0	1	0
12
13	Get column			5
14				11
15				17
16	Get sum			33
Sheet1

Cell Formulas
Range	Formula
D3:I5	D3	=SEQUENCE(3,6)
B6	B6	=SUM(XLOOKUP(1,(D1:I1=B4)*(D2:I2=B5),D3#))
D9:I10	D9	=D1:I1=B4
D11:I11	D11	=D9#*D10#
D13:D15	D13	=XLOOKUP(1,D11#,D3#)
D16	D16	=SUM(D13#)
Dynamic array formulas.

 

[StephenCrump]

both of these worked perfectly!

By the way, it would be helpful if you updated your account details to show which version of Excel you're using, as the best solution may vary by version

If FILTER() works for you, then presumably you have 365 or 2021. But there are some functions in 365 not available in 2021, hence it would be good for us to know your version.