Calculating sales with respect to item and in a particular month

Hi, I would like to ask, if A1:A100 are the types of products (e.g. "CD", "Book" and "Tape") and B1:B100 are the selling price of the respective item, what formula should I key in in cell C1 if I want it to show me the total sales of all the CDs only? (note that the CDs are all in different prices)

In addition, if the items were bought over a 1 year period and D1:D12 are used to represent the 12 months, what formula should i have in E1 if I want to know the total sales of CDs for the month of Jan?

Originally Posted by Fotis1991

Hi

Try

1) =SUMIF(A1:A100,"CD",B1:B100)

2)SUMPRODUCT((A1:A100="CD")*(MONTH(D1:D100=1))*(B1:B100))

Hi Fortis, thanks for your reply, I managed to get the first one right. But for the 2nd one, it came up with a #VALUE! error. Would you know what actually went wrong?

Originally Posted by Fotis1991

Hi

Try

1) =SUMIF(A1:A100,"CD",B1:B100)

2)SUMPRODUCT((A1:A100="CD")*(MONTH(D1:D100=1))*(B1:B100))

Hi Fortis, I managed to get the 2nd eqn to work similarly to the 1st eqn. It will correctly select those with "CD" but irregardless of the month of the year. Would you know what actually went wrong?

Originally Posted by Fotis1991

Did you see my post number 4?

Oh I missed it. Managed to get it already. Many thanks. =)

Hi Fotis, one more question on this topic. How would the formula change if the data spans over several years and I want to know the sales for CDs for each month and year?

I got this formula from another spreadsheet, can I use it?

=SUMPRODUCT(--(Investment!$B$3:$B$602>=$A3), --(Investment!$B$3:$B$602<DATE(YEAR($A3),MONTH($A3)+1,1)), Investment!F$3:F$602)

Hi Fotis, apologies for the late reply. I am unable to upload the file but i roughly know what had gone wrong. Lets say I have a E1:E100 showing the months and years from 2010 to 2012 and I want the corresponding CD sales for each of the months in F1:F10, what should I replace # with in the formula below so that I can achieve that without having to type the months and years out manually?

=SUMPRODUCT((A1:A100="CD")*(MONTH(D1:D100)=#)*(year(d1:d100)=#)*(B1:B100))

Got it! Thanks alot Fotis! =)