How do I calculate with SUMPRODUCT in Excel

Hi

I have a spreadsheet with shops and dates. I can calculate and group by month how often someone has visited a shop, but do not know how to sum and group the extra charges for all shops (G2:G13) or for each shop (I2:I13) and others. Can someone help me?

Thanks
Massimo

Shop1.PNG
Shopping.xls
Massimo ScolaAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
For all shops try this in G2

=SUMPRODUCT((MONTH(0&OrderDate)=ROWS(F$2:F2))*(YEAR(0&OrderDate)=2011),C$2:C$29)

and in I2

=SUMPRODUCT((MONTH(0&OrderDate)=ROWS(F$2:F2))*(YEAR(0&OrderDate)=2011)*(Shop=H$1),C$2:C$29)

and similar in other cells, see attached

regards, barry
Shopping-barry.xls
0
 
wrt1meaCommented:
Well, I was going to try and answer this....

Nice formula Barry.
0
 
Massimo ScolaAuthor Commented:
Thanks a lot! Appreciate your help
Massimo
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.