Forum Discussion
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 (...
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)
Wow SergeiBaklan !! 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.