Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 29th 11, 04:40 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 9
Default Using COUNTIF with criteria in an adjacent column?

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!

  #2   Report Post  
Old July 29th 11, 04:59 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 1,549
Default Using COUNTIF with criteria in an adjacent column?

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"
wrote in message
...
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!



  #3   Report Post  
Old August 5th 11, 04:59 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 9
Default Using COUNTIF with criteria in an adjacent column?

On Jul 29, 11:59*am, "Jim Cone" wrote:
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"
wrote in ...







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!


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
  #4   Report Post  
Old August 5th 11, 06:24 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 1,549
Default Using COUNTIF with criteria in an adjacent column?


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"
wrote in message
...
On Jul 29, 11:59 am, "Jim Cone" wrote:
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"
wrote in ...







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!


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


  #5   Report Post  
Old August 14th 11, 04:10 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 9
Default Using COUNTIF with criteria in an adjacent column?

On Aug 5, 1:24*pm, "Jim Cone" wrote:
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"
wrote in ...
On Jul 29, 11:59 am, "Jim Cone" wrote:









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"
wrote in ...


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!


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


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!


  #6   Report Post  
Old August 16th 11, 05:01 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 9
Default Using COUNTIF with criteria in an adjacent column?

On Aug 5, 1:24*pm, "Jim Cone" wrote:
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"
wrote in ...
On Jul 29, 11:59 am, "Jim Cone" wrote:









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"
wrote in ...


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!


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


Thanks, Jim, that did it!

C


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
COUNTIF: 2 criteria: Date Range Column & Text Column MAC Excel Worksheet Functions 14 September 16th 08 04:39 PM
Using CountIf with non-adjacent cells in same column RS Excel Discussion (Misc queries) 3 June 28th 07 05:45 PM
countif = < AND value in adjacent columns match criteria crafty_girl Excel Worksheet Functions 3 April 27th 06 08:40 PM
summing values from adjacent column with refrence from adjacent column Pivotrend Excel Discussion (Misc queries) 6 March 4th 06 11:24 AM
Sum column if multiple criteria are met in adjacent cells GateKeeper Excel Worksheet Functions 5 September 4th 05 05:04 AM


All times are GMT +1. The time now is 10:03 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017