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

Thom,

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

For specific data,

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

HTH

Steve

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

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

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

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

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