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.

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.

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.

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.

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"
^^^^^^^^^^^^^

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"
^^^^^^^^^^^^^

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

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"
^^^^^^^^^^^^^

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
---