Sumifs with criteria from Dynamic Lists

[scojax22]

Location	Department	Year	Month	Value
Main	Internal Medicine	2023​	Jan	2,101
Main	Internal Medicine	2023​	Feb	1,833
Main	Internal Medicine	2024​	Jan	2,138
Main	Internal Medicine	2024​	Feb	-
Park	Internal Medicine	2023​	Jan	-
Park	Internal Medicine	2023​	Feb	500
Park	Internal Medicine	2023​	Mar	-
Park	Internal Medicine	2024​	Jan	178
Park	Internal Medicine	2024​	Feb	-
Main	Pediatrics	2023​	Jan	2,411
Main	Pediatrics	2023​	Feb	2,150
Main	Pediatrics	2024​	Jan	1,915
Main	Pediatrics	2024​	Feb	-
Park	Pediatrics	2023​	Jan	-
Park	Pediatrics	2023​	Feb	-
Park	Pediatrics	2024​	Jan	1,018
Park	Pediatrics	2024​	Feb	-
Holmdel	Dental	2023​	Jan	2,475
Holmdel	Dental	2023​	Feb	2,283
Holmdel	Dental	2024​	Jan	2,657
Holmdel	Dental	2024​	Feb	-
Holmdel	Pediatrics	2023​	Jan	542
Holmdel	Pediatrics	2023​	Feb	365
Holmdel	Pediatrics	2024​	Jan	478
Holmdel	Pediatrics	2024​	Feb	-

I have the above Table. I want to use Sumifs to add the values for multiple Locations and Department data, which will be linking to Dynamic lists. So my list(s) may have 1 location and 1 department or 5 locations and 3 departments, etc. I was using something like this, but it was giving me inaccurate results:

=sum(SUMIFS(EncounterData[Value],EncounterData[Location],UNIQUE(FILTER(O1:O5,O1:O5<>"")),EncounterData[Department],UNIQUE(FILTER(P1:P5,P1:P5<>"")),EncounterData[Month],M1))

Any help would be greatly appreciated.

 

[Fluff]

How about

=sum(SUMIFS(EncounterData[Value],EncounterData[Location],UNIQUE(FILTER(O1:O5,O1:O5<>"")),EncounterData[Department],torow(UNIQUE(FILTER(P1:P5,P1:P5<>""))),EncounterData[Month],M1))

When using 2 arrays one has to vertical & the other horizontal.

 

[scojax22]

Brilliant. I don't need for my current purposes, bu would you be able to add a 3rd array to the equation, or no because you now have a vertical and a horizontal?

Either way, thanks!

 

[Fluff]

No, you can only have two arrays in sumifs, but you could use the filter function, or sumproduct if you need more.

 

[Dave Patton]

You did not indicate your reporting requirement or provide examples of the results that you require.

Two additional ideas using a Table of your data are:
1. result at top based on the criteria selected.
2. Pivotby function which is a Beta function; I do not know when it will be available for regular Excel 365.

Sumifs.xlsm
	A	B	C	D	E
1	Total based on the Filtered Data or no Filters				23,044
2	Location	Department	Year	Month	Value
3	Main	Internal Medicine	2023	1	2,101
4	Main	Internal Medicine	2023	2	1,833
5	Main	Internal Medicine	2024	1	2,138
6	Main	Internal Medicine	2024	2	-
7	Park	Internal Medicine	2023	1	-
8	Park	Internal Medicine	2023	2	500
9	Park	Internal Medicine	2023	3	-
10	Park	Internal Medicine	2024	1	178
11	Park	Internal Medicine	2024	2	-
12	Main	Pediatrics	2023	1	2,411
13	Main	Pediatrics	2023	2	2,150
14	Main	Pediatrics	2024	1	1,915
15	Main	Pediatrics	2024	2	-
16	Park	Pediatrics	2023	1	-
17	Park	Pediatrics	2023	2	-
18	Park	Pediatrics	2024	1	1,018
19	Park	Pediatrics	2024	2	-
20	Holmdel	Dental	2023	1	2,475
21	Holmdel	Dental	2023	2	2,283
22	Holmdel	Dental	2024	1	2,657
23	Holmdel	Dental	2024	2	-
24	Holmdel	Pediatrics	2023	1	542
25	Holmdel	Pediatrics	2023	2	365
26	Holmdel	Pediatrics	2024	1	478
27	Holmdel	Pediatrics	2024	2	-
7c

Cell Formulas
Range	Formula
E1	E1	=SUBTOTAL(109,$E$3:$E$26)

Sumifs.xlsm
	A	B	C	D	E	F	G	H
1
2
3	Main	Internal Medicine	2023	1	2,101
4	Park	Pediatrics	2024	2	1,018
5	Holmdel	Dental		3	2,475
6
7	N.B. The following is a Beta function; it is not in regular versions of Excel.
8			2023	2023	2023	2024	2024	Total
9			1	2	3	1	2
10	Holmdel	Dental	2,475	2,283		2,657	0	7,415
11	Holmdel	Pediatrics	542	365		478	0	1,385
12	Main	Internal Medicine	2,101	1,833		2,138	0	6,072
13	Main	Pediatrics	2,411	2,150		1,915	0	6,476
14	Park	Internal Medicine	0	500	0	178	0	678
15	Park	Pediatrics	0	0		1,018	0	1,018
16	Total		7,529	7,131	0	8,384	0	23,044
17
7c_

Cell Formulas
Range	Formula
A3:A5	A3	=UNIQUE(DataT[Location])
B3:B5	B3	=UNIQUE(DataT[Department])
C3:C4	C3	=UNIQUE(DataT[Year])
D3:D5	D3	=UNIQUE(DataT[Month])
E3	E3	=SUMIFS(DataT[Value],DataT[Location],A3,DataT[Department],B3,DataT[Year],C3,DataT[Month],D3)
E4	E4	=SUMIFS(DataT[Value],DataT[Location],A4,DataT[Department],B4,DataT[Year],C4,DataT[Month],D3)
E5	E5	=SUMIFS(DataT[Value],DataT[Location],A5,DataT[Department],B5,DataT[Year],C3,DataT[Month],D3)
A8:H16	A8	=PIVOTBY(DataT[[Location]:[Department]],DataT[[Year]:[Month]],DataT[Value],SUM)
Dynamic array formulas.