Countif

**omar.b** · 05-30-2007, 05:03 AM

I need to create a formula which will count the number of cells in a particular range which contain a certain value, but only count the ones where a corresponding cell in another column is blank.

i.e Count all cells in column j which contain the value "review" but only if the corresponing cell in row b is blank

Many thanks

**sweep** · 05-30-2007, 05:11 AM

Hi,

This array formula will do what you require, presuming your data is in B1:B11 and J1:J11.

=COUNT(IF(ISBLANK(B1:B11),IF(J1:J11="review",1,""),""))

paste this into your workbook, and press SHIFT, CTRL and ENTER to confirm it. It should appear in the bar like this:

{=COUNT(IF(ISBLANK(B1:B11),IF(J1:J11="review",1,""),""))}

**daddylonglegs** · 05-30-2007, 05:13 AM

Perhaps

=SUMPRODUCT(--(B1:B100=""),--(J1:J100="Review"))

adjust ranges as necessary

**omar.b** · 05-30-2007, 05:25 AM

tried both, neither work.

I think i'm just a dunce.

I need to count the number of cells with the text REVIEW in them in column J, but only count them if the corresponding field in row M is blank. So if J9 has "review" in it but M9 has a date in it, I don't want it counted.

However if J11 has got REVIEW in it and M11 is blank I want that to be added.

**sweep** · 05-30-2007, 05:28 AM

you were talking about columns B and J before:

=COUNT(IF(ISBLANK(B1:B11),IF(M1:M11="review",1,"") ,"")) with CTRL, SHIFT and ENTER.

If that doesn't work, please zip and post an example.

Dave

**omar.b** · 05-30-2007, 05:40 AM

sorry, about that, having a hard day.

Very grateful for your help.

Just one more, what if I now want to count the number of cells in column J with Review in but now only if the corresponding cell in Column M contains a date (any date)

Many thanks

**sweep** · 05-30-2007, 05:50 AM

No problem - you've gotta have hard days to know what an easy one feels like!

=COUNT(IF(ISBLANK(M1:M11),"",IF(J1:J11="review",1,""))), again with CTRL, SHIFT and ENTER

will display as {=COUNT(IF(ISBLANK(M1:M11),"",IF(J1:J11="review",1,"")))}

Let me know if it works?

Dave

**daddylonglegs** · 05-30-2007, 05:57 AM

Originally Posted by omar.b

what if I now want to count the number of cells in column J with Review in but now only if the corresponding cell in Column M contains a date (any date)

Try

=SUMPRODUCT(--ISNUMBER(M1:M100),--(J1:J100="Review"))