Countif Formula

  • Thread starter Thread starter Lime
  • Start date Start date
L

Lime

This has to be easy...
column "J" I need to count how many times "Alabama" shows in column "I" only
if column "H" = either "HOUSES" or "CONDOS" or "Apartments"

ANy help would be appreacited.
 
Lime said:
This has to be easy...
column "J" I need to count how many times "Alabama" shows in column "I" only
if column "H" = either "HOUSES" or "CONDOS" or "Apartments"

ANy help would be appreacited.
Click to expand...


=SUMPRODUCT(((H1:H10="HOUSES")+(H1:H10="CONDOS")+(H1:H10="Apartments"))*(I1:I10="Alabama"))
 
Try this...

Use cells to hold the criteria:

A1 = Alabama
B1 = Houses
B2 = Condos
B3 = Apartments

=SUMPRODUCT(--(I1:I5=A1),--(ISNUMBER(MATCH(H1:H5,B1:B3,0))))
 
Hi,

=if(or(H1:H1000="HOUSES",H1:H1000="CONDOS"),count(I1:I1000,"Alabama","")

if neither houses or condos are in column H the formula will display a blank
 
Glenn said:
=SUMPRODUCT(((H1:H10="HOUSES")+(H1:H10="CONDOS")+(H1:H10="Apartments"))*(I1:I10="Alabama"))
Click to expand...

Or this:

=SUMPRODUCT(((H1:H10={"HOUSES","CONDOS","Apartments"}))*(I1:I10="Alabama"))
 
Couldn't get that to work...
Hi,

=if(or(H1:H1000="HOUSES",H1:H1000="CONDOS"),count(I1:I1000,"Alabama","")

if neither houses or condos are in column H the formula will display a blank
Click to expand...
 
T. Valko said:
Try this...

Use cells to hold the criteria:

A1 = Alabama
B1 = Houses
B2 = Condos
B3 = Apartments

=SUMPRODUCT(--(I1:I5=A1),--(ISNUMBER(MATCH(H1:H5,B1:B3,0))))
Click to expand...


I like that. Is there a reason for your syntax over this?

=SUMPRODUCT((I1:I5=A1)*(ISNUMBER(MATCH(H1:H5,B1:B3,0))))
 
Is there a reason for your syntax over this?

It's slightly more efficient, especially on big ranges.
 
Hi,

Here's another approache

=SUMPRODUCT((I1:I6=A1)*(H1:H6=B1:D1))

Where A1 contains Alabama, and B1:D1 your 3 types of housing.

direction is important.
 
Back
Top