Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default Count cells based on whether another cell is blank or not

I have a spreadsheet with data in 2 columns and I want to be able to
figure out how many times there is data in col A and Col B of each
row. If either cell is empty I don't want it counted.

Basically I want to count the amount of times that there is data in
col a and col b along the same row.

Thanks.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default Count cells based on whether another cell is blank or not

=SUMPRODUCT(--(A2:A500<""),--(B2:B500<""))

will do this, note that unless you have Excel 2007 you can't use the whole
column

--


Regards,


Peo Sjoblom

"luvnrocs" wrote in message
...
I have a spreadsheet with data in 2 columns and I want to be able to
figure out how many times there is data in col A and Col B of each
row. If either cell is empty I don't want it counted.

Basically I want to count the amount of times that there is data in
col a and col b along the same row.

Thanks.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Count cells based on whether another cell is blank or not

One way
Something like this, in say C1:
=SUMPRODUCT(--((A1:A10<"")+(B1:B10<"")0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,900 Files:354 Subscribers:53
xdemechanik
---
"luvnrocs" wrote in message
...
I have a spreadsheet with data in 2 columns and I want to be able to
figure out how many times there is data in col A and Col B of each
row. If either cell is empty I don't want it counted.

Basically I want to count the amount of times that there is data in
col a and col b along the same row.

Thanks.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default Count cells based on whether another cell is blank or not

That would be either A or B not A and B
To me it sounds as if the OP wants A and B

"Basically I want to count the amount of times that there is data in
col a and col b along the same row"
^^^^^^^^^^^^^

--


Regards,


Peo Sjoblom

"Max" wrote in message
...
One way
Something like this, in say C1:
=SUMPRODUCT(--((A1:A10<"")+(B1:B10<"")0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,900 Files:354 Subscribers:53
xdemechanik
---
"luvnrocs" wrote in message
...
I have a spreadsheet with data in 2 columns and I want to be able to
figure out how many times there is data in col A and Col B of each
row. If either cell is empty I don't want it counted.

Basically I want to count the amount of times that there is data in
col a and col b along the same row.

Thanks.





  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Count cells based on whether another cell is blank or not

Agreed, my suggestion was focusing on this OP's line:
.. If either cell is empty I don't want it counted.


To me, it sounds from the line that OP wants either A or B
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,900 Files:354 Subscribers:53
xdemechanik
---
"Peo Sjoblom" wrote in message
...
That would be either A or B not A and B
To me it sounds as if the OP wants A and B

"Basically I want to count the amount of times that there is data in
col a and col b along the same row"
^^^^^^^^^^^^^





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Count cells based on whether another cell is blank or not

But adding them together the way you did will counted if either one is
true... I read (as did Peo) the OP as saying he does *not* want that....
either empty, *don't* count it.

Rick

"Max" wrote in message
...
Agreed, my suggestion was focusing on this OP's line:
.. If either cell is empty I don't want it counted.


To me, it sounds from the line that OP wants either A or B
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,900 Files:354 Subscribers:53
xdemechanik
---
"Peo Sjoblom" wrote in message
...
That would be either A or B not A and B
To me it sounds as if the OP wants A and B

"Basically I want to count the amount of times that there is data in
col a and col b along the same row"
^^^^^^^^^^^^^




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Count cells based on whether another cell is blank or not

Rick,

Shall we leave it to the OP to clarify what s/he really wants, since what's
originally posted is kinda ambiguous? One of the 2 suggestions given should
fit the bill, with the other held as a solution for the other scenario.

P/s: Peo's response wasn't visible to me before I responded (our responses
were only 6 minutes apart)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,900, Files: 354, Subscribers: 53
xdemechanik
---
"Rick Rothstein (MVP - VB)" wrote in
message ...
But adding them together the way you did will counted if either one is
true... I read (as did Peo) the OP as saying he does *not* want that....
either empty, *don't* count it.

Rick



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default Count cells based on whether another cell is blank or not

On Jul 16, 7:23 pm, "Max" wrote:
Agreed, my suggestion was focusing on this OP's line:

.. If either cell is empty I don't want it counted.


To me, it sounds from the line that OP wants either A or B
--

Thank you both Peo and Max. I ended up using Peo's formula and it
works great.

Jenn



Max
Singaporehttp://savefile.com/projects/236895
Downloads:15,900 Files:354 Subscribers:53
xdemechanik
---"Peo Sjoblom" wrote in message

...

That would be either A or B not A and B
To me it sounds as if the OP wants A and B


"Basically I want to count the amount of times that there is data in
col a and col b along the same row"
^^^^^^^^^^^^^


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Count cells based on whether another cell is blank or not

Thank you both Peo and Max. I ended up using Peo's formula and it
works great.


Welcome. Thanks for feeding back here, and clarifying.
You can hold my suggestion to apply for the other scenario, should it ever
be encountered <g
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,000, Files: 354, Subscribers: 53
xdemechanik
---


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
Count with multiple criteria based on cell not blank? Twishlist Excel Worksheet Functions 7 January 13th 10 07:16 PM
Count from Blank & Non-Blank Cells Mandeep Dhami Excel Discussion (Misc queries) 4 February 12th 08 04:25 PM
Count the non blank cells vijaydsk1970 Excel Worksheet Functions 3 November 10th 06 01:04 PM
Do Not count blank cells imjustme Excel Discussion (Misc queries) 4 November 1st 05 05:44 PM
COUNT ONLY CELLS THAT AREN'T BLANK paulinec Excel Worksheet Functions 8 January 8th 05 02:51 AM


All times are GMT +1. The time now is 01:30 PM.

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

About Us

"It's about Microsoft Excel"