counting across many criteria (combining AND and OR conditions)

[Moggins]

I everyone,

I am new to using Excel to this level and I am trying to do a complex count where there are a combination of AND and OR criteria.

I have managed similar things by using =count(if( and entering the formula as an array, however, this particular scenario seems to have too many variants. I have also tried using =sum and =sumproduct. I have achieved the first part of my query by using =sum but the formula is very long and messy - I'm sure there must be a better/easier way to achieve the result.

Using the example data in the table below, I am trying to achieve 2 different counts:-

Count where:-
Column A = 1 AND column D = Fruit AND Columns E-G = Apple OR Pear OR Orange OR Raspberry OR Lime OR Banana (the answer I'm looking for is 9)

I then need to take this further and do as per the above AND add a further element where columns H:J = Red OR Green (to provide the result of 5)

1
1	02/01/12		Vegetable	Leek	Cabbage	Potato
1	03/01/12		Fruit	Apple	Apple	Apple	Green	Red	Green
1	04/01/12		Fruit	Pear	Orange	Raspberry
2	05/02/12		Fruit	Lime	Apple	Banana		Red
2	06/02/12		Fruit	Orange	Pear	Lemon
2	07/02/12		Fruit	Apple	Orange	Banana	Red

<tbody>
[TD="width: 100"]01/01/12[/TD]
[TD="width: 41"][/TD]
[TD="width: 100"]Fruit[/TD]
[TD="width: 96"]Apple[/TD]
[TD="width: 96"]Apple[/TD]
[TD="width: 96"]Pear[/TD]
[TD="width: 56"]Red[/TD]
[TD="width: 56"]Green[/TD]
[TD="width: 56"][/TD]

</tbody>

Any assistance would be much appreciated.
Thank you.

 

[Skybluekid]

Try using count ifs

 

[Moggins]

Thanks for your reply...I'm sorry I should have said I am running Excel 2003. There are too many criteria for a nested if and the countifs feature is newer than 2003 I believe?

 

[Skybluekid]

Are you counting across or down columns?

 

[Skybluekid]

How does this look:


=COUNTIF(A2:A2,1)+COUNTIF(D2:D2,"Fruit")+COUNTIF(E2:E2,"Apple")+COUNTIF(F2:F2,"Apple")+COUNTIF(G2:G2,"Pear")

 

[Moggins]

Are you counting across or down columns?

Looking across each line and then moving down to do the same thing on the next row so that the total sum (based on the 1 in column A) will be all the entries for a given month (1 = Jan, 2 = Feb etc).

 

[Moggins]

Thanks - I'll give it a go and get back to you

 

[Moggins]

It didn't like that - it produced an error (I tried it as an array too). Also, (and this is probably me not explaining things well enough) there are too many variables as cells E, F and G could have any one of the 6 different types of fruit and cells H, I and J any one of 2 different colours so using line one as an example it would need to count if A1=1 and D1=Fruit and E1 = either apple, pear, banana, orange, lime, raspberry and the same for F1 and G1 and then if H1, i1 or J1 contained either red or green....its quite hard to explain but hopefully this makes sense?

 

[Skybluekid]

try =COUNTIF(A2:A2,1)+COUNTIF(D2:D2,INDEX(F10:F11,MATCH(E2,F10:F11,0)))+COUNTIF(E2:E2,INDEX(G10:G15,MATCH(E2,G10:G15,0)))+COUNTIF(F2:F2,INDEX(G10:G15,MATCH(F2,G10:G15,0)))+COUNTIF(G2:G2,INDEX(G10:G15,MATCH(G2,G10:G15,0)))

The INDEX and Match ranges are a list of all the possible variables

 

[Skybluekid]

alternative

=COUNTIF(A2,1)+COUNTIF(D2,INDEX(F10:F11,MATCH(E2,F10:F11,0)))+COUNTIF(E2,INDEX(G10:G15,MATCH(E2,G10:G15,0)))+COUNTIF(F2,INDEX(G10:G15,MATCH(F2,G10:G15,0)))+COUNTIF(G2,INDEX(G10:G15,MATCH(G2,G10:G15,0)))