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.

 

[Moggins]

Thanks, will get back to you once I've had a chance to test it.

 

[circledchicken]

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.

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)

Any assistance would be much appreciated.
Thank you.

Hi and welcome to the forum,

Here are a couple of alternatives you can try (note the data set-up is slightly different to your description but the concept is the same regardless).

Data set-up:

Sheet1[TABLE="class: html-maker-worksheet"]<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]Column Heading A[/TD]
[TD]Column Heading B[/TD]
[TD]Column Heading C[/TD]
[TD]Column Heading D[/TD]
[TD]Column Heading E[/TD]
[TD]Column Heading F[/TD]
[TD]Column Heading G[/TD]
[TD]Column Heading H[/TD]
[TD]Column Heading I[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD="align: right"]1[/TD]
[TD="align: right"]01/01/2012[/TD]
[TD]Fruit[/TD]
[TD]Apple[/TD]
[TD]Apple[/TD]
[TD]Pear[/TD]
[TD]Red[/TD]
[TD]Green[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD="align: right"]1[/TD]
[TD="align: right"]02/01/2012[/TD]
[TD]Vegetable[/TD]
[TD]Leek[/TD]
[TD]Cabbage[/TD]
[TD]Potato[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD="align: right"]1[/TD]
[TD="align: right"]03/01/2012[/TD]
[TD]Fruit[/TD]
[TD]Apple[/TD]
[TD]Apple[/TD]
[TD]Apple[/TD]
[TD]Green[/TD]
[TD]Red[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD="align: right"]1[/TD]
[TD="align: right"]04/01/2012[/TD]
[TD]Fruit[/TD]
[TD]Pear[/TD]
[TD]Orange[/TD]
[TD]Raspberry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD="align: right"]2[/TD]
[TD="align: right"]05/02/2012[/TD]
[TD]Fruit[/TD]
[TD]Lime[/TD]
[TD]Apple[/TD]
[TD]Banana[/TD]
[TD][/TD]
[TD]Red[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD="align: right"]2[/TD]
[TD="align: right"]06/02/2012[/TD]
[TD]Fruit[/TD]
[TD]Orange[/TD]
[TD]Pear[/TD]
[TD]Lemon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD="align: right"]2[/TD]
[TD="align: right"]07/02/2012[/TD]
[TD]Fruit[/TD]
[TD]Apple[/TD]
[TD]Orange[/TD]
[TD]Banana[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[/TR]</tbody>[/TABLE]
Excel 2010

Criteria and result set-up:

Excel Workbook
	K	L	M	N	O	P
1	Column Heading A - Criteria	Column Heading C - Criteria	Column Heading G-I - Criteria	Column Heading D-F - Criteria	Answer Question 1	Answer Question 2
2	1	Fruit	Red	Apple	9	5
3			Green	Pear	9	5
4				Orange	9	5
5				Raspberry
6				Lime
7				Banana
Sheet1

Excel 2010

Cell Formulas
Range	Formula
O2	=SUMPRODUCT( (A2:A8=K2)* (C2:C8=L2)* COUNTIF(N2:N7,D2:F8))
O3	=SUMPRODUCT( (A2:A8=K2)* (C2:C8=L2)* ISNUMBER(MATCH(D2:F8,N2:N7,0)))
P2	=SUMPRODUCT( (A2:A8=K2)* (C2:C8=L2)* COUNTIF(N2:N7,D2:F8)* COUNTIF(M2:M3,G2:I8))
P3	=SUMPRODUCT( (A2:A8=K2)* (C2:C8=L2)* ISNUMBER(MATCH(D2:F8,N2:N7,0))* ISNUMBER(MATCH(G2:I8,M2:M3,0)))

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[Moggins]

Thank you so much - I've had several suggestions of how to tackle this but unfortunately, as yet have not had time to try them - I'm very grateful for the assistance regardless. Hopefully at least one of the the suggested approaches will work but regardless of outcome I am always happy to try different approaches to formula and learn from the results.

I will try to use each suggested solution over the next day or so and see how I get on. I will let everyone know. Thank you again for your help - Like many others, I am completely self taught in excel and get little opportunity to learn methodically so support from more experienced users is much appreciated when I come up against specific issues such as this. Thank you.