How tu use structured reference as criteria in SUMIFS function

New Contributor

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


5 Replies
best response confirmed by JorgeElizondom1963 (New Contributor)


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)

@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))



One more for the collection


in G3:

in F4:

in G4:
(drag down)

not sure how to make one spill if without combining

Wow @Sergei Baklan !!  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.