Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
cyberindio
 
Posts: n/a
Default 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   Report Post  
Biff
 
Posts: n/a
Default

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   Report Post  
cyberindio
 
Posts: n/a
Default

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   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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   Report Post  
Bob Phillips
 
Posts: n/a
Default

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   Report Post  
Biff
 
Posts: n/a
Default

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   Report Post  
Bob Phillips
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Countif using format criteria....not number criteria? Troy Excel Worksheet Functions 1 April 20th 05 04:50 AM
Can I get the mode, min, and max with multiple criteria? BobT Excel Discussion (Misc queries) 1 February 15th 05 04:20 AM
Countif formula with multiple criteria ie >30 and <60? Dali Excel Worksheet Functions 2 January 7th 05 05:49 PM
Countif with multiple criteria and multiple worksheets JJ Excel Worksheet Functions 1 December 28th 04 07:37 PM
Counting "rows", i.e. simultaneous criteria for multiple cells gkline Excel Worksheet Functions 2 November 19th 04 08:30 AM


All times are GMT +1. The time now is 01:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"