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?

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:

=AND(D2="abc",OR(AO2=1,AS2=1))+0

Another option would be to adapt your COUNTIFS formula like this:

=COUNTIFS(ccs_export!$D:$D,"abc",ccs_export!$AO:$AO,1)
    +COUNTIFS(ccs_export!$D:$D,"abc",ccs_export!$AS:$AS,1)
    -COUNTIFS(ccs_export!$D:$D,"abc",ccs_export!$AO:$AO,1,ccs_export!$AS:$AS,1)

This option would not be practical for the potential combinations of 15 COUNTIFS() functions.

Another option would be to use SUMPRODUCT.

=SUMPRODUCT(--(ccs_export!$D:$D="abc"),--(((ccs_export!$AO:$AO=1)+(ccs_export!$AS:$AS=1))>0))

If you go for the SUMPRODUCT option, try not to use whole column references because it will slow your workbook down.

Yes, here's another SUMPRODUCT example to show you how to expand the formula to 4 columns.

=SUMPRODUCT(--(ccs_export!$D2:$D100="abc"), 
       --((   (ccs_export!$AO2:$AO100=1) + (ccs_export!$AQ2:$AQ100=1) + (ccs_export!$AS2:$AS100=1) + (ccs_export!$AY2:$AY100=1)    )>0))

Here's one way of several:

=SUMPRODUCT( 
    --(ccs_export!$D2:$D100="abc") , 
    --(((ccs_export!$E2:$E100="XYZ")+(ccs_export!$E2:$E100="CDE"))=0) , 
    etc..... )

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