SOLVED

How tu use structured reference as criteria in SUMIFS function

%3CLINGO-SUB%20id%3D%22lingo-sub-1665702%22%20slang%3D%22en-US%22%3EHow%20tu%20use%20structured%20reference%20as%20criteria%20in%20SUMIFS%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1665702%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EI%20have%20a%20table%20(named%20SalesData)%20with%20three%20columns%3A%20Sales%20Date%20(as%20dd%2Fmm%2Fyy)%2C%20Region%20and%20Amount%20and%20thousands%20of%20entries.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20build%20a%20summary%20table%20that%20has%20the%20years%20as%20columns%20(2019%2C2020%2C%20etc)%20and%20Regions%20as%20rows.%3C%2FP%3E%3CP%3EFor%20some%20very%20specific%20reasons%20this%20cannot%20be%20a%20pivot%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20a%20SUMIFS%20function%20with%20two%20criteria%20for%20each%20year%2FRegion%20combination.%20My%20question%20is%20how%20to%20properly%20use%20Structered%20References%20inside%20the%20SUMIFS%20function.%20I%20want%20something%20like%20SalesData%5BRegion%5D%3D%22Northwest%22%20and%20Year(SalesData%5BDate%5D)%3D2019.%3C%2FP%3E%3CP%3EFor%20the%20life%20of%20me%20I%20cant%20seem%20to%20make%20it%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1665702%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1665758%22%20slang%3D%22en-US%22%3ERe%3A%20How%20tu%20use%20structured%20reference%20as%20criteria%20in%20SUMIFS%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1665758%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F793264%22%20target%3D%22_blank%22%3E%40JorgeElizondom1963%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIFS(%3CSUM%20reference%3D%22%22%3E%2C%26nbsp%3B%3CSPAN%3E%26nbsp%3BSalesData%5BRegion%2C%22Northwest%22%2C%20Year(SalesData%5BDate%5D)%2C2019)%3C%2FSPAN%3E%3C%2FSUM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1665826%22%20slang%3D%22en-US%22%3ERe%3A%20How%20tu%20use%20structured%20reference%20as%20criteria%20in%20SUMIFS%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1665826%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F793264%22%20target%3D%22_blank%22%3E%40JorgeElizondom1963%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20cannot%20use%20SUMIFS%20(unless%20you%20create%20a%20separate%20column%20in%20the%20SalesData%20table%20for%20the%20year).%20Use%20SUMPRODUCT%20instead%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S3522.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F218199i8408821FF8DBC580%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22S3522.png%22%20alt%3D%22S3522.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%0A%3CP%3EIn%20the%20screenshot%2C%20the%20regions%20are%20in%20E2%3AE5%20and%20the%20years%20in%20F1%3AH1.%3C%2FP%3E%0A%3CP%3EThe%20formula%20in%20F2%20is%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT(SalesData%5B%5BAmount%5D%3A%5BAmount%5D%5D%2C(SalesData%5B%5BRegion%5D%3A%5BRegion%5D%5D%3D%24E2)*(YEAR(SalesData%5B%5BSales%20Date%5D%3A%5BSales%20Date%5D%5D)%3DF%241))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20can%20be%20filled%20to%20the%20right%20then%20down%20(or%20vice%20versa)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1666291%22%20slang%3D%22en-US%22%3ERe%3A%20How%20tu%20use%20structured%20reference%20as%20criteria%20in%20SUMIFS%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1666291%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F793264%22%20target%3D%22_blank%22%3E%40JorgeElizondom1963%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20more%20for%20the%20collection%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20528px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F218226iE321AF9805ED78C5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3Ein%20G3%3A%0A%3DTRANSPOSE(UNIQUE(YEAR(SalesData%5BDate%5D)))%0A%0Ain%20F4%3A%0A%3DSORT(UNIQUE(SalesData%5BRegion%5D))%0A%0Ain%20G4%3A%0A%3DMMULT(%0A%20%20%20%20SEQUENCE(1%2CCOUNTA(SalesData%5BDate%5D)%2C1%2C0)%2C%0A%20%20%20%20SalesData%5BSales%5D*%0A%20%20%20%20(YEAR(SalesData%5BDate%5D)%3DG%243%23)*%0A%20%20%20%20(SalesData%5BRegion%5D%3D%24F4)%0A)%0A(drag%20down)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Enot%20sure%20how%20to%20make%20one%20spill%20if%20without%20combining%3C%2FP%3E%3C%2FLINGO-BODY%3E
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)
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.