Sep 14 2020 11:25 AM
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
Sep 14 2020 12:17 PM
SolutionYou 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)
Sep 14 2020 12:32 PM
@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))
Sep 14 2020 02:12 PM
One more for the collection
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
Sep 14 2020 03:46 PM
Wow @Sergei Baklan !! I got more than what I bargained for.
Learned a lot from your reply.
Thank you very much!!
Sep 14 2020 03:58 PM
@JorgeElizondom1963 , you are welcome. Each of us is finding something new here and that's great.
Sep 14 2020 12:17 PM
SolutionYou 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)