Dynamic month selection to SUMIF (Tried XLOOKUP)

[Jmorrison67]

Good morning MrExcel community,

I'm looking for a bit of help with writing a formula to do a SUMIF but based on a dynamic month selection potentially embedding XLOOKUP in to it. I've created an example workbook to show you what I'm looking for.

Basically i have to SUM up values from a data set for Month but not setting the SUMIF SUM range to a particular column (as want to automate the process as much as possible and dont want the users having to move the columns range each period), but want the formula to lookup based on criteria such as: "MonthActual11" > see workbook. I'm comfortable with doing an XLOOKUP itself, but trying to get the SUMIF & XLOOKUP done is a bit tricky.

Sheet 2 is where the output / formula should be. Results for UK and USA by Month for Actual, Budget, LYA. Formula above each is then linked to the pink section which will change for each reporting period.

Sheet 1 is just the data set with the lookup formula added to it.

Tried to upload the excel but extension isnt allowed - let me know if can send it over?


Any help would be appreciated

Kind regards
Jmorrison67

 

[DanteAmor]

Use the following formulas, one for the UK and another for the USA, essentially it is the same formula, only the country's acronym must be placed in each formula.

varios 05dic2024.xlsm
	A	B	C	D	E	F
1
2		Current Month
3		11
4
5
6
7
8
9			UK	Actual	Budget	LYA
10				Month
11				Actual	Budget	LYA
12			123abc	306	462	600
13			456def	330	495	660
14			789ghi	0	0	0
15
16
17			USA	Actual	Budget	LYA
18				Month
19				Actual	Budget	LYA
20			123abc	351	507	645
21			456def	375	540	708
22			789ghi	0	0	0
Sheet2

Cell Formulas
Range	Formula
D12:F14	D12	=SUMPRODUCT((Sheet1!$D$4:$AM$4=$B$3)*(Sheet1!$D$5:$AM$5=D$11)*(Sheet1!$B$6:$B$36=$C12)*(Sheet1!$C$6:$C$36=$C$9)*(Sheet1!$D$6:$AM$36))
D20:F22	D20	=SUMPRODUCT((Sheet1!$D$4:$AM$4=$B$3)*(Sheet1!$D$5:$AM$5=D$11)*(Sheet1!$B$6:$B$36=$C20)*(Sheet1!$C$6:$C$36=$C$17)*(Sheet1!$D$6:$AM$36))

 

[djclements]

What version of Excel are you using? Your profile indicates 2016, but your screenshots look a lot like MS365.

If you have Excel for MS365, you could try the following formula in cell D12:

=LET(
    curr_mnth, B3,
    criteria1, C9,
    criteria2, C12:C14,
    crit_rng1, Sheet1!C6:C36,
    crit_rng2, Sheet1!B6:B36,
    vals, Sheet1!D6:AM36,
    cols, SEQUENCE(3,,curr_mnth*3-2),
    DROP(REDUCE("",cols,LAMBDA(a,v,HSTACK(a,SUMIFS(INDEX(vals,,v),crit_rng1,criteria1,crit_rng2,criteria2)))),,1)
)

Change the value in cell C9 from "UK" to "USA" and the results will update dynamically.

Alternatively, if the new GROUPBY function is available to you:

=GROUPBY(Sheet1!B5:B36,CHOOSECOLS(Sheet1!D5:AM36,SEQUENCE(,3,B3*3-2)),SUM,3,0,,Sheet1!C5:C36=C9)

Or, an all-in-one report with subtotals by "Site":

=GROUPBY(CHOOSECOLS(Sheet1!B5:C36,2,1),CHOOSECOLS(Sheet1!D5:AM36,SEQUENCE(,3,B3*3-2)),SUM,3,2)