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