# How to use CountIF() with a condition?

1. ## How to use CountIF() with a condition?

I have a range of multi column data and use CountIf() to count the number of occurrences of text match a particular critera.
For example counting how many times "abc" occurs in the a range of 21 different columns. I have this part working fine.

BUT, we now also need to know if there is an "x" entered in the 3rd column to the right of the cell in which the match is found and; Count only if the value is found.

Below is the part that is working.

My issue is figuring out how to put in a condition so that the cell is not counted unless there is also an "x" in a cell 3 columns to the right, that has me stumped.

-DL

2. ## Re: How to use CountIF() with a condition?

Try countifS

Notice the offset by 3 columns on the 2nd range (\$D\$4:\$AE\$54).
You may need to adjust that depending on exactly what you mean by "3 columns to the right"

Take column A, is 3 columns to the right C or D ?
If you say D is 3 columns to the right of A, then the formula as posted is correct.
If you say C, then adjust the 2nd range by 1 column to \$C\$4:\$AD\$54

3. ## Re: How to use CountIF() with a condition?

=SUMPRODUCT(--(\$A\$4:\$AB\$54=1),--(OFFSET(\$A\$4:\$AB\$54,0,3)="x")) should work

4. ## Re: How to use CountIF() with a condition?

Hi Jonmo1 and martindwilson

Thanks for your replies. I don't think either will work. the data looks like this:

Item Lbl1 Lbl2 Chk'd Item Lbl1 Lbl2 Chk'd Item Lbl1 Lbl2 Chk'd
abc widget lidget x abc widget zidget dfg widget zidget
cdf widget lidget cdf widget zidget x cdf widget zidget x
abc widget lidget x abc widget zidget x abc widget zidget x

So moving the range 3 cells would only work for the left most group. I think we need tosomehow know the cell ID of the cell that CountIf is evaluating and then offset.

-DL

5. ## Re: How to use CountIF() with a condition?

Works for me, see formula in AD1
EFdlowrey.xlsx

6. ## Re: How to use CountIF() with a condition?

no that offsets every cell in the range and looks at it
=SUMPRODUCT(--(\$A\$4:\$AB\$54="abc"),--(OFFSET(\$A\$4:\$AB\$54,0,3)="x"))
with the above pasted into a4 to l7 gives a count of 4 for abc

7. ## Re: How to use CountIF() with a condition?

Wow,

You guys are genius. That works great.

Thank you!

-DL

8. ## Re: How to use CountIF() with a condition?

You're welcome.

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