How to get the SUM of a Multi-Criteria Filter to spill

[Ngamia]

Hi All,

I am automating some spreadsheets and was looking for a way to get the results in F2:F4 to update dynamically based on the contents in the table. The sales table will change on an ongoing basis and as this feeds into another sheet, forgetting to update the total sales column leads to a cascade of issues.
Alternative approaches are welcome.

Excel Issue.xlsx
	A	B	C	D	E	F	G
1	SALES DATE	VENDOR	QUANTITY		VENDOR	TOTAL SALES
2	10/26/22	K50001	1000		K50001	1400	#N/A
3	10/26/22	K50002	1000		K50002	1200
4	10/26/22	K50003	600		K50003	1300
5	10/26/22	K50001	700
6	10/27/22	K50001	300		DATE
7	10/27/22	K50002	700		10/27/22
8	10/27/22	K50003	700
9	10/27/22	K50001	1100
10	10/27/22	K50002	500
11	10/27/22	K50003	400
12	10/27/22	K50003	200
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=UNIQUE(SALES[VENDOR])
G2	G2	=SUM(FILTER(SALES[QUANTITY],(E2#=SALES[VENDOR])*(SALES[SALES DATE]=DATE)))
F2:F4	F2	=SUM(FILTER(SALES[QUANTITY],(E2=SALES[VENDOR])*(SALES[SALES DATE]=DATE)))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
DATE	=Sheet1!$E$7	G2, F2:F4

 

[etaf]

does it not already update as you are using a table reference , and when new data appears in the table the formula should update - providing the date changes
or is it the date field that needs to update

how is the date E7 created ?

although using that field mayget splill errors if you get more codes
can that be moved to H1 ?

 

[Fluff]

How about

=SUMIFS(Sales[QUANTITY],Sales[VENDOR],E2#,Sales[SALES DATE],E7)

 

[Ngamia]

does it not already update as you are using a table reference , and when new data appears in the table the formula should update - providing the date changes
or is it the date field that needs to update

how is the date E7 created ?

although using that field mayget splill errors if you get more codes
can that be moved to H1 ?

The date in E7 is read from different sheet.

 

[etaf]

ok, i see Fluff has given an alternative formula

The date in E7 is read from different sheet.

unless its changed to a different location - unique() will spill if 5 codes found

 

[Ngamia]

How about

=SUMIFS(Sales[QUANTITY],Sales[VENDOR],E2#,Sales[SALES DATE],E7)

This works perfectly. Thanks Fluff.

 

[Fluff]

Glad we could help & thansk for the feedback.