Count cells based on whether another cell is blank or not

[luvnrocs]

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.

 

[Peo Sjoblom]

=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

 

[Max]

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

 

[Peo Sjoblom]

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]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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

 

[luvnrocs]

Agreed, my suggestion was focusing on this OP's line:


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]

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