SUMPRODUCT counting occurrences of multiple conditions - HELP!!

[cjcass]

Hi,

Wonder if anyone can help?

I have set up a formula to count the occurrences of multiple conditions in a table of data (see below..) the formula reads from 4 cells of criteria but I will not always use the 4 criteria and maybe only need to use say 2 criteria - when I do this my formula doesn't work - my example below shows this... How do I make the formula continue to work when some criteria cells don't have any entries?

This table is a simple example but my main work will have a much larger table and probably about 10 criteria cells, so running a big formula with 10 nested IF statements will probably be too cumbersome... does anyone know a smart workround for this?

Thanks for your time,
Chris

Fruit Colours Towns Names
Apples Red London Harry
Apples Green Paris Bert
Pears Blue London Fred
Plums Red Venice Steve
Apples Red London Bert


Criteria Result
Fruit Pears 0
Colours
Towns London
Names Fred


The formula in my Result cell is
=SUMPRODUCT((A2:A6=B10)*(B2:B6=B11)*(C2:C6=B12)*(D2:D6=B13))
If I put 'Blue' into the Colour criteria the Result is 1
The top left cell (Fruit) is A1..

[cjcass]

sorry, here's the pic which is clearer...

pic1.jpg