Forum Discussion

JorgeElizondom1963's avatar
JorgeElizondom1963
Copper Contributor
Sep 14, 2020
Solved

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 (...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Sep 14, 2020

    JorgeElizondom1963 

    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)

mtarler's avatar
mtarler
Silver Contributor
Sep 14, 2020

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

 

Resources