COUNTIF with multiple criteria?

[cyberindio]

I have a table with a column that indicates if the record is for a "House" or
an "Apartment" and a second column that indicates if it is "Occupied" or
"Vacant". How do I wite a formula to count all the houses that are vacant ?

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(A1:A100="House"),--(B1:B100="Vacant"))

Biff

 

[cyberindio]

Thanks Biff, but I can't seem to get it to work. I get a "0" answer when
there have to be at least 1500 records that fit the criteria. I used the
exact syntax you gave me but it might be multiplying zeros.

 

[JE McGimpsey]

Are you sure you adjusted the ranges correctly?

No leading or trailing spaces?

 

[Bob Phillips]

Try

=SUMPRODUCT(--(Trim(A1:A100)="House"),--(Trim(B1:B100)="Vacant"))


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[^'^BatAttaK^'^]

Try

=SUMPRODUCT(--(Trim(A1:A100)="House"),--(Trim(B1:B100)="Vacant"))

What does the trim do?

 

[Biff]

Hi!

TRIM removes leading and trailing spaces.

Hey, did you ever get that unique count problem solved? I lost that thread
and couldn't follow up any further.

Biff

 

[Bob Phillips]

It will ensure that all of the values tested against "House" and "Vacant" do
not have leading or trailing spaces, thereby causing the test to fail.

Did it solve your problem?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[^'^BatAttaK^'^]

Hi!

TRIM removes leading and trailing spaces.

Hey, did you ever get that unique count problem solved? I lost that thread
and couldn't follow up any further.

Biff

Thanks for asking!

I was out of town and wasn't able to follow up either. Sorry about
that.

Nope...I was never able to make it work. It worked like a charm in
that sample but once I tried to use it in a real world situation it
just would not work. I can't post the spreadsheet that it is failing
on as it contains confidential info.