Multiply based on criteria

Hi

Im trying to make a formula that will multiply all numbers in an area based on a criteria, for instance multiply the numbers in column B in the below table based on the criteria "Blue" in column A. The formula should give the result 12, ie 2*3*2. Im familiar with the SUMPRODUCT but that will add the 3 numbers, ie 2+3+2, and I have also been messing with the PRODUCT formula and those 2 together, but I cant seem to make it work, so it multiplies the 3 numbers.

Any help greatly appreciated

	A	B
1	Blue	2
2	Red	2
3	Blue	3
4	Red	3
5	Blue	2

Another way to do it is to use a Pivot Table.

1. Give your data column headings ('Colour' and 'Count' for example).
2. Click any cell in the data area, go to the Insert tab and click 'Pivot Table' on the left-hand-side. The default option is for the Pivot Table to go on a new worksheet, but you can put it elsewhere on the same sheet if you want.
3. On the right-hand-side, select both 'Colour' and 'Count'.
4. You'll now see a table which has one column headed 'Sum of Count' with the values 7 and 5 shown against Blue and Red. Click either the 7 or 5, then go to 'Summarize Values By' (on the Pivot Table Tools | Options tab). Select 'Product' - the column will now read 'Product of Count' and have the values 12 and 6.

Hope that's of some help too.


Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

Thank you both very much - this was just what I was looking for.

I gave you both rep, but as davesexcel got there first I will mark his answer as the solved one. Also thanks for the Pivot suggestion Aardigspook, but the array will work for me.

Hmn, thought I could put in another criteria like below with AND, but that is not working, can that be done?

In this example the 2nd criteria is that B1:B5 should be less than 3, ie the result should be 2*2=4

{=PRODUCT(IF(AND(B1:B5<3;A1:A5="Blue"),B1:B5)}

Glad to be of help and thanks for the rep.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

your suggestion works like a charm Tony, thanks