COUNTIF or SUMPRODUCT
 

I have a large spread sheet and I need to count the cells with data in Column
A but only if there is also data in Column B. Can you help?
--
Retired but Alive,

BigT

COUNTIF or SUMPRODUCT
 

Thom,

=SUMPRODUCT((A1:A1000<"")*(B1:B1000<""))

For specific data,

=SUMPRODUCT((A1:A1000=Your Data Here)*(B1:B1000=Your Data Here))

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=560446

COUNTIF or SUMPRODUCT
 

By "something in column B" do you mean not blank or a specific value?

The formula below will count cells if A & B are non-blank (not empty).

=SUMPRODUCT((A2:A200"")*(B2:B200""))

Or

=SUMPRODUCT((A2:A200)*(B2:B200=condition))

HTH

"ThomH" wrote:

I have a large spread sheet and I need to count the cells with data in Column
A but only if there is also data in Column B. Can you help?
--
Retired but Alive,

BigT

COUNTIF or SUMPRODUCT
 

Thanks for your reply. I should have been more specific. What I want is to
count the cells in column A but I only want to count the cells in column A if
the same row has data in column B. My spreadsheet will have many rows of
data in column A but it will not have data in many of the rows of column B.
I am only interested in counting the rows in column if there is data in
column B.
--
Retired but Alive,

BigT


"Toppers" wrote:

By "something in column B" do you mean not blank or a specific value?

The formula below will count cells if A & B are non-blank (not empty).

=SUMPRODUCT((A2:A200"")*(B2:B200""))

Or

=SUMPRODUCT((A2:A200)*(B2:B200=condition))

HTH

"ThomH" wrote:

I have a large spread sheet and I need to count the cells with data in Column
A but only if there is also data in Column B. Can you help?
--
Retired but Alive,

BigT

COUNTIF or SUMPRODUCT
 

Thom,

Do you mean that even if A is blank if B has data, count that row or
count B if there is data?

=SUMPRODUCT(--(B1:B10<""))

If you are using more than one range, the ranges have to be the same
number of rows or columns for it to work. If you want to count B if
you find specific data like the number 1 in A then try,

=SUMPRODUCT((A1:A10=1)*(B1:B10<""))

If this is not what you mean and you want the count to be contingent
upon *ANY* data existing in both A and B then my original post should
work for you.

Note: If your data consists of text and numbers then using "" won't
work because numbers (unless stored as text) are not recognized as
being greater than "".



HTH
Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=560446

COUNTIF or SUMPRODUCT
 

Thank you for your help. What you gave me is what I needed.
--
Retired but Alive,

BigT


"SteveG" wrote:


Thom,

Do you mean that even if A is blank if B has data, count that row or
count B if there is data?

=SUMPRODUCT(--(B1:B10<""))

If you are using more than one range, the ranges have to be the same
number of rows or columns for it to work. If you want to count B if
you find specific data like the number 1 in A then try,

=SUMPRODUCT((A1:A10=1)*(B1:B10<""))

If this is not what you mean and you want the count to be contingent
upon *ANY* data existing in both A and B then my original post should
work for you.

Note: If your data consists of text and numbers then using "" won't
work because numbers (unless stored as text) are not recognized as
being greater than "".



HTH
Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=560446

COUNTIF or SUMPRODUCT
 

You're welcome. Glad to hear you got the solution you were looking
for.
Cheers,
Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=560446