We help IT Professionals succeed at work.

How do I calculate with SUMPRODUCT in Excel

Massimo Scola
on
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
Comment
Watch Question

Most Valuable Expert 2013
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

Commented:
Well, I was going to try and answer this....

Nice formula Barry.
Massimo ScolaSoftware Engineer

Author

Commented:
Thanks a lot! Appreciate your help
Massimo