Using COUNTIF with criteria in an adjacent column?

M

Mr Molio

Is this possible? I have a structure like this:

A B
-------------------
5/10
5/10 6/10
5/12 5/25
5/15
5/16 5/30

I'd like to count the number of entries in column A only if the
adjacent cell in column B is blank. So, in the example above, I'd want
to see a count of 2 as the answer.

Can I use the COUNTIF for this, maybe with OFFSET? Or do I need to do
a database function? Seems like it ought to be easy, but I'm missing
something...

Thanks for any help!
 
M

Mr Molio

Why not just count the blanks in column B... =COUNTBLANK(B1:B10)
--
Jim Cone
Portland, Oregon USA .http://www.mediafire.com/PrimitiveSoftware.
(free and commercial excel programs)

"Mr Molio" <[email protected]>
wrote in message
Click to expand...

Thanks, Jim. Let me add a wrinkle to that - the range in both A & B
will be changing - expanding or shrinking from day to day. I'd like,
in a cell at the top of the sheet, to always be able to tell how many
cells in B are blank. Is there a way to use the COUNTBLANK with an
expanding range?

The range always expands by the user adding to column A, then other
users need to go in and fill in values in column B. I need to report
each day how many "unfinished" cells there are in Column B. So, could
I do something that would basically do this > COUNTBLANK (B1: "to the
total number of filled cells in A")? I could put a COUNTA(A:A)
somewhere else on the sheet, but not sure how to get it's value into
the range in the COUNTBLANK function?

THanks!
C
 
J

Jim Cone

Almost missed your response, I usually take the flags off my messages after 4 days.
Try this (assumes you may/may not have captions in one or both columns)...

=COUNTA(A2:A10000)-COUNTA(B2:B10000)
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Data Rows add-in: Custom shading, deleting, inserting)





"Mr Molio" <[email protected]>
wrote in message
Why not just count the blanks in column B... =COUNTBLANK(B1:B10)
--
Jim Cone
Portland, Oregon USA .http://www.mediafire.com/PrimitiveSoftware.
(free and commercial excel programs)

"Mr Molio" <[email protected]>
wrote in message
Click to expand...

Thanks, Jim. Let me add a wrinkle to that - the range in both A & B
will be changing - expanding or shrinking from day to day. I'd like,
in a cell at the top of the sheet, to always be able to tell how many
cells in B are blank. Is there a way to use the COUNTBLANK with an
expanding range?

The range always expands by the user adding to column A, then other
users need to go in and fill in values in column B. I need to report
each day how many "unfinished" cells there are in Column B. So, could
I do something that would basically do this > COUNTBLANK (B1: "to the
total number of filled cells in A")? I could put a COUNTA(A:A)
somewhere else on the sheet, but not sure how to get it's value into
the range in the COUNTBLANK function?

THanks!
C
 
M

Mr Molio

Almost missed your response, I usually take the flags off my messages after 4 days.
Try this (assumes you may/may not have captions in one or both columns)....

   =COUNTA(A2:A10000)-COUNTA(B2:B10000)
--
Jim Cone
Portland, Oregon USA  .http://www.mediafire.com/PrimitiveSoftware .
(Data Rows add-in: Custom shading, deleting, inserting)

"Mr Molio" <[email protected]>
wrote in message


Thanks, Jim. Let me add a wrinkle to that - the range in both A & B
will be changing - expanding or shrinking from day to day. I'd like,
in a cell at the top of the sheet, to always be able to tell how many
cells in B are blank. Is there a way to use the COUNTBLANK with an
expanding range?

The range always expands by the user adding to column A, then other
users need to go in and fill in values in column B. I need to report
each day how many "unfinished" cells there are in Column B. So, could
I do something that would basically do this > COUNTBLANK (B1: "to the
total number of filled cells in A")? I could put a COUNTA(A:A)
somewhere else on the sheet, but not sure how to get it's value into
the range in the COUNTBLANK function?

THanks!
C
Click to expand...

Thanks, I'll give it a try. Sorry for not responding sooner - I'm
blocked from internet use at work, so I have to pick up what I can in
the evenings!
 
M

Mr Molio

Almost missed your response, I usually take the flags off my messages after 4 days.
Try this (assumes you may/may not have captions in one or both columns)....

   =COUNTA(A2:A10000)-COUNTA(B2:B10000)
--
Jim Cone
Portland, Oregon USA  .http://www.mediafire.com/PrimitiveSoftware .
(Data Rows add-in: Custom shading, deleting, inserting)

"Mr Molio" <[email protected]>
wrote in message


Thanks, Jim. Let me add a wrinkle to that - the range in both A & B
will be changing - expanding or shrinking from day to day. I'd like,
in a cell at the top of the sheet, to always be able to tell how many
cells in B are blank. Is there a way to use the COUNTBLANK with an
expanding range?

The range always expands by the user adding to column A, then other
users need to go in and fill in values in column B. I need to report
each day how many "unfinished" cells there are in Column B. So, could
I do something that would basically do this > COUNTBLANK (B1: "to the
total number of filled cells in A")? I could put a COUNTA(A:A)
somewhere else on the sheet, but not sure how to get it's value into
the range in the COUNTBLANK function?

THanks!
C
Click to expand...

Thanks, Jim, that did it!

C
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

List all tables, including document structure 0
Finding the right style field in VBA 0
HELP: IF Statement between Dates for Reverse Index failing 0
Finding the proper style field and the numbered paragraph value within the style 0
Modifying a complex date formual to count months 4
Keyboard Mistyping - as if a computer virus - only happens in Outlook (www.portal.office.com) 0
How do I get started using the Countif, Countifs option with Excel 2
Finding the weighted average of text that have been given values 1

Top