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.?

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?

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?

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.``
Another option would be to adapt your COUNTIFS formula like this:
``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.

5. Re: Countifs Multiple Criteria

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

6. Re: Countifs Multiple Criteria

The only problem with this is that SumProduct is very slow.

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.``

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.

9. Re: Countifs Multiple Criteria

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

10. Re: Countifs Multiple Criteria

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

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

12. Re: Countifs Multiple Criteria

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

http://xldynamic.com/source/xld.SUMPRODUCT.html

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1