COUNTIF with multiple criteria?
 

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

Hi!

Try this:

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

Biff
"cyberindio" wrote in message
...
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
?
--
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.

"Biff" wrote:

Hi!

Try this:

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

Biff
"cyberindio" wrote in message
...
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
?
--
cyberindio

Are you sure you adjusted the ranges correctly?

No leading or trailing spaces?

In article ,
"cyberindio" wrote:

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.

Try

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


--

HTH

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


"cyberindio" wrote in message
...
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.

"Biff" wrote:

Hi!

Try this:

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

Biff
"cyberindio" wrote in message
...
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
?
--
cyberindio

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

"^'^BatAttaK^'^" wrote in message
...
On Mon, 25 Apr 2005 22:42:57 +0100, "Bob Phillips"
wrote:

Try

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

What does the trim do?

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^'^" wrote in message
...
On Mon, 25 Apr 2005 22:42:57 +0100, "Bob Phillips"
wrote:

Try

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

What does the trim do?