COUNTIF or SUMPRODUCT

T

ThomH

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

SteveG

Thom,

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

For specific data,

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

HTH

Steve
 
T

Toppers

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
 
T

ThomH

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

SteveG

Thom,

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

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

If you are using more than one range, the ranges have to be the sam
number of rows or columns for it to work. If you want to count B i
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 contingen
upon *ANY* data existing in both A and B then my original post shoul
work for you.

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



HTH
Stev
 
Top