how do i countif on multiple criterea?

[Guest]

I would like to know how to count if 2 criterea are true in a range of cells

 

[Bob Phillips]

=SUMPRODUCT(--(rng1="text"),--(rng2=number))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I would like to know how to count if 2 criterea are true in a range of

cells

 

[Guest]

TY

=SUMPRODUCT(--(rng1="text"),--(rng2=number))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)


cells

 

[Guest]

TY

 

[Roger Govier]

Hi

Continuing with what Bob told you, you can add as many criteria as you
wish
=SUMPRODUCT(--(rng1="text"),--(rng2=number),--(rng3=number),--(rng4="text"))

Each test will return True or False.
The double unary minus will coerce True's to 1's and False's to 0's.
Multiplying them together, it is only all 1's that will return a value
of 1, any 0 will make the whole sum 0.
So Sumproduct is adding only the values where all tests are true, and
therefore giving your Count.