# Countifs Multiple Criteria

1. ## Countifs Multiple Criteria

Hi,

I have the following formula.
=COUNTIFS(ccs_export!\$D:\$D,"abc",ccs_export!\$AO:\$AO,1)+COUNTIFS(ccs_export!\$D:\$D,"abc",ccs_export!\$AS:\$AS,1)

Column D, Column AO, Column AS
abc ,1,1
abc ,1,1
cdf ,0,1
cdh ,1,0
abc ,0,0

Is this the correct formula for expressing the following

IF Column D is "abc" and column AO is 1, or Column AS is 1, count 1.?  Register To Reply

2. ## Re: Countifs Multiple Criteria

Hi,

Is this the correct formula for expressing the following

IF Column D is "abc" and column AO is 1, or Column AS is 1, count 1.?
Yes, but a potential issue with that formula is that if both AO and AS equal 1, then 2 will be counted (one for each). Is that what you want?  Register To Reply

3. ## Re: Countifs Multiple Criteria

No that is not what I want.
If one of them is 1. Then count only 1.
In fact I'm using 15 countifs, I just entered 2 as an example.
Basically if one of those columns has 1, then count 1.
How do I achieve this?  Register To Reply

4. ## Re: Countifs Multiple Criteria

One way would be to add a helper column which returns 1 for each row if any of the relevant values equal 1. Then a simple SUM formula can sum up the 1s and 0s in that helper column. The helper column formula would be something like this, copied down the column: ``Please Login or Register  to view this content.`` ``Please Login or Register  to view this content.``
This option would not be practical for the potential combinations of 15 COUNTIFS() functions.

Another option would be to use SUMPRODUCT. ``Please Login or Register  to view this content.``
If you go for the SUMPRODUCT option, try not to use whole column references because it will slow your workbook down.  Register To Reply

5. ## Re: Countifs Multiple Criteria

So basically since I can't add an extra column, Sumproduct would solve the problem?  Register To Reply

6. ## Re: Countifs Multiple Criteria

The only problem with this is that SumProduct is very slow.  Register To Reply

7. ## Re: Countifs Multiple Criteria

Yes, here's another SUMPRODUCT example to show you how to expand the formula to 4 columns. ``Please Login or Register  to view this content.``  Register To Reply

8. ## Re: Countifs Multiple Criteria

Now lets say that in addition to Column D, i also need to Add Column E and EXCLUDE everything in that column that says XYZ and CDE.  Register To Reply

9. ## Re: Countifs Multiple Criteria

Here's one way of several: ``Please Login or Register  to view this content.``  Register To Reply

10. ## Re: Countifs Multiple Criteria

To exclude I assume I would use <>?
Also the =0 is important?  Register To Reply

11. ## Re: Countifs Multiple Criteria

Hi,
To exclude I assume I would use <>?
No, what I posted excludes them.

Also the =0 is important?
Yes, it's important. Instead of using a whole column, let's understand that part of the formula by using a single cell, say A1.

Starting with this:
((A1="XYZ")+(A1="CDE"))

If A1 contains either XYZ or CDE then that formula will return 1. If A1 contains neither XYZ nor CDE then the formula returns 0. So, to exclude XYZ and CDE, we equate that result to 0.

((A1="XYZ")+(A1="CDE"))=0

That's what I did in the formula, except for a whole column:

((ccs_export!\$E2:\$E100="XYZ")+(ccs_export!\$E2:\$E100="CDE"))=0  Register To Reply

12. ## Re: Countifs Multiple Criteria

See this site for a great explaination on the SUMPRODUCT function.

http://xldynamic.com/source/xld.SUMPRODUCT.html  Register To Reply