Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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? |
#7
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif using format criteria....not number criteria? | Excel Worksheet Functions | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) | |||
Countif formula with multiple criteria ie >30 and <60? | Excel Worksheet Functions | |||
Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions | |||
Counting "rows", i.e. simultaneous criteria for multiple cells | Excel Worksheet Functions |