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 wrote:
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"))

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

--
Biff
Microsoft Excel MVP


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

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

"Lime" wrote:

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 wrote:
Lime wrote:
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"))


Or this:

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

Couldn't get that to work...

Eduardo wrote:
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

"Lime" wrote:

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.

T. Valko wrote:
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))))

Is there a reason for your syntax over this?

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


--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
T. Valko wrote:
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))))

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

"Glenn" wrote:

Glenn wrote:
Lime wrote:
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"))


Or this:

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

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.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Lime" wrote:

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.