counting non blank cells according to a condition

H

hayleyho

Hi there, I have a question that has been driving me nuts so if anyone can
assist, it would be much appreciated.
My spreadsheet looks like

A B
C
1 Weymouth 6/5/09 5/9/06
2 Dorchester 12/1/09 3/2/04

What I want to be able to do, is to say, "if A1 equals weymouth, and B1 is
not blank, count it".

Any ideas? Solving this will make a currently long winded manual counting
process take seconds for me at work. Any help appreciated!!
 
R

Ragdyer

You could, of course, also use this function to count an entire range:

=SUMPRODUCT((A1:A100="Weymouth")*(B1:B100<>""))
 
O

OssieMac

Expanding on the answer you can count the entire range like this.

=SUMPRODUCT((A1:A12="Weymouth")*(B1:B12<>""))
 
S

Shane Devenshire

Hi,

In 2007 you could use

=COUNTIFS(A1:A100,"Weymouth",B1:B100,"<>")

instead of

=SUMPRODUCT((A1:100="Weymouth")*(B1:B100<>""))

And if you entered Weymouth in F1 then both formulas would be more dynamic.

=COUNTIFS(A1:A100,F1,B1:B100,"<>")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to count non blank cells with a condition? 1
Need Array Formula for Counting Blank & Non Blank Cells (Multiple Criteria) 3
counting blank cells in a range 2
Counting Non Blank Cells 4
Count Non-Blank Cells in Column If a Reference Criteria Met. 5
sumproduct with additions to data 1
Count from Blank & Non-Blank Cells 2
Validation List with 2 criterias 1

Top