Forum Discussion
How tu use structured reference as criteria in SUMIFS function
Hi all,
I have a table (named SalesData) with three columns: Sales Date (as dd/mm/yy), Region and Amount and thousands of entries.
I want to build a summary table that has the years as columns (2019,2020, etc) and Regions as rows.
For some very specific reasons this cannot be a pivot table.
I am trying to use a SUMIFS function with two criteria for each year/Region combination. My question is how to properly use Structered References inside the SUMIFS function. I want something like SalesData[Region]="Northwest" and Year(SalesData[Date])=2019.
For the life of me I cant seem to make it work.
Please help
You cannot use SUMIFS (unless you create a separate column in the SalesData table for the year). Use SUMPRODUCT instead:
In the screenshot, the regions are in E2:E5 and the years in F1:H1.
The formula in F2 is
=SUMPRODUCT(SalesData[[Amount]:[Amount]],(SalesData[[Region]:[Region]]=$E2)*(YEAR(SalesData[[Sales Date]:[Sales Date]])=F$1))
This can be filled to the right then down (or vice versa)
5 Replies
One more for the collection
in G3: =TRANSPOSE(UNIQUE(YEAR(SalesData[Date]))) in F4: =SORT(UNIQUE(SalesData[Region])) in G4: =MMULT( SEQUENCE(1,COUNTA(SalesData[Date]),1,0), SalesData[Sales]* (YEAR(SalesData[Date])=G$3#)* (SalesData[Region]=$F4) ) (drag down)not sure how to make one spill if without combining
Wow SergeiBaklan !! I got more than what I bargained for.
Learned a lot from your reply.
Thank you very much!!
JorgeElizondom1963 , you are welcome. Each of us is finding something new here and that's great.
JorgeElizondom1963 The SUMIFS() statement doesn't use a conditional statement(s) but instead has criteria RANGEs and criteria. Hence you can't use YEAR(range) = "2019". I believe each of the following options should work:
=SUMIFS(sum_range, SalesData[Region], "Northwest", SalesData[Date], ">"&DATE( 2019,1,1), SalesData[Date], "<"&DATE(2020,1,1))
=SUMPRODUCT(sum_range *(SalesData[Region] = "Northwest") * (YEAR(SalesData[Date])=2019))
=SUM(FILTER(sum_range, (SalesData[Region] = "Northwest") * (YEAR(SalesData[Date])=2019))
You cannot use SUMIFS (unless you create a separate column in the SalesData table for the year). Use SUMPRODUCT instead:
In the screenshot, the regions are in E2:E5 and the years in F1:H1.
The formula in F2 is
=SUMPRODUCT(SalesData[[Amount]:[Amount]],(SalesData[[Region]:[Region]]=$E2)*(YEAR(SalesData[[Sales Date]:[Sales Date]])=F$1))
This can be filled to the right then down (or vice versa)
