Countif Formula

[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.

 

[Glenn]

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.

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

 

[T. Valko]

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))))

 

[Eduardo]

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]

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

Or this:

=SUMPRODUCT(((H1:H10={"HOUSES","CONDOS","Apartments"}))*(I1:I10="Alabama"))

 

[Glenn]

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

 

[Glenn]

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))))

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

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

 

[T. Valko]

Is there a reason for your syntax over this?

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

 

[Lime]

Thanks... It worked GREAT!!!!!

Or this:

=SUMPRODUCT(((H1:H10={"HOUSES","CONDOS","Apartments"}))*(I1:I10="Alabama"))

 

[Shane Devenshire]

Hi,

Here's another approache

=SUMPRODUCT((I1:I6=A1)*(H1:H6=B11))

Where A1 contains Alabama, and B11 your 3 types of housing.

direction is important.