Count cells based on multiple OR criteria

[David627]

I need to have multiple ranges count based on several criteria. The hitch is that needs to count if one or more criteria is met not all. I tried using a COUNTIFS however that seems to evaluate based on AND not OR.

Is there a nested COUNTIFS or similar function that would work?

 

[c_m_s_jr]

Have you explored using SUMPRODUCT? A sample of your data could also be helpful to give the best answer.

 

[David627]

Have you explored using SUMPRODUCT?

SUMPROSDUCT won't work since I want to count not sum

 

[mrhat187]

Thought I read somewhere you couldn't nest using count.

 

[David627]

I have nested with a count - I've used ISERROR(COUNT) for example, however that wouldn't apply here

 

[David627]

I tried =COUNTIFS(All!F2:F1120,"--",All!H2:H1120,"X",All!I2:I1120,"TRUE",All!J2:J1120,"FALSE") however this is a very tedious work around

 

[titekinou]

Actually SUMPRODUCT works with count..
I used it a lot in the last few days, and you shoul give it a try =D

 

[David627]

As I understand SUMPRODUCT it will sum (don't see how it does count in MS documenattion) a certain range of cells or multiple ranges of cells. I need to base my ranges on a criteria which I don't believe SUMPRODUCT can do.

My formula will need to look for a specific value in a range or another value in another range. This is why COUNTIFS was my intial consideration since I can have multiple ranges with multiple criteria - the issue is it takes the multiple criteria as an AND condition versus an OR condition which is what I am seeking.

 

[texasalynn]

here is sumproduct as a count

Excel Workbook
	A	B	C	D	E
1	627	A		count of A's with 627
2	627	B		2
3	627	A
4	627	B
5	628	A
6	628	B
7
Sheet1

 

[pgc01]

Hi David

It would be easier if you just state your problem and post an example with inputs, outputs and the logic.

There may be many types of solutions you are not aware of, and if we understand the problem we may suggest some.

One case is using Sumproduct() that, as you see in the example, can count and include conditions with And's and Or's.