OR Criteria in CountIfs

[SidRout]

Please see the image attached.

I would like to find the count where Col A = "Apple" and (Col B = "Green" or Col C = "Green" or Col D = "Green"). The count for example I am looking for is 3.

I have been wrecking my brains with countifs, sumproduct but unable to achieve the result.

 

[Faseeh]

Please see attached sheet: Hope This helps.

 

[SidRout]

Thanks Faseeh

Yes I am aware how to achieve it with helper columns or with a user defined function (VBA). However, I am looking for a way to achieve it directly.

 

[Faseeh]

Try this with CSE:

=SUMPRODUCT((A2:A7="Apple")*(SUMPRODUCT(((B2:D7="Green")*1))>0)*1)

 

[SidRout]

Awesome! Yes that does it. Now let me understand it!

 

[Faseeh]

..should you stuck somewhere as you try to understand it, kindly let us know!

 

[SidRout]

In fact yes, I do have a question. I am trying to understand the formula.

In the original question, we had Col B,C,D together. What if there is a column in between B & C which had some other data so how would I change B2:D7="Green" to some thing like B2:B7,D2:E7="Green"?

Please see this image.

PS: It is ok, if you reply by tomorrow. It is pretty late in this side of the planet and I am off to bed. Thanks again for your willingness to help.

 

[narunfca]

=SUMPRODUCT((A2:A7="Apple")*(SUMPRODUCT(((B2:D7="Green")*1))>0)*1)

The above formula is not giving the results, check by deleting the "green" in H2 in 5th data row. It simply counts and do not check row by row.

The more accurate formula may be
=SUMPRODUCT((A2:A7="Apple")*((B2:B7="Green")+(C2:C7="Green")+(D2:D7="Green")>0))

The above formula will work even when there is blank in between columns.

with best regards
Arun N

 

[Faseeh]

Arun's formula is correct. please stick to it!

Thanks @Arun

 

[SidRout]

=SUMPRODUCT((A2:A7="Apple")*(SUMPRODUCT(((B2:D7="Green")*1))>0)*1)

The above formula is not giving the results, check by deleting the "green" in H2 in 5th data row. It simply counts and do not check row by row.

The more accurate formula may be
=SUMPRODUCT((A2:A7="Apple")*((B2:B7="Green")+(C2:C7="Green")+(D2:D7="Green")>0))

The above formula will work even when there is blank in between columns.

with best regards
Arun N

Thanks Arun. It makes more sense now and easier for me to understand.

I thank you both for helping me out