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
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,""),""))}
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
Perhaps
=SUMPRODUCT(--(B1:B100=""),--(J1:J100="Review"))
adjust ranges as necessary
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.
Last edited by omar.b; 05-30-2007 at 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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks