SOLVED

How tu use structured reference as criteria in SUMIFS function

Copper 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 (Copper Contributor)
Solution

@JorgeElizondom1963 

You cannot use SUMIFS (unless you create a separate column in the SalesData table for the year). Use SUMPRODUCT instead:

 

S3522.png

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

 

@JorgeElizondom1963 

One more for the collection

image.png

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

1 best response

Accepted Solutions
best response confirmed by JorgeElizondom1963 (Copper Contributor)
Solution

@JorgeElizondom1963 

You cannot use SUMIFS (unless you create a separate column in the SalesData table for the year). Use SUMPRODUCT instead:

 

S3522.png

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)

View solution in original post