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)

HansVogelaar's avatar
HansVogelaar
MVP
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)

Resources