I have a formula that gives me the average sales price for properties that
fall within a certain value. I would like to break it down further by
designating these same averages by state rather than just overall. Here is
my existing formula:

=(SUMPRODUCT(--($E$2:$E$49550000),--($E$2:$E$495<75001),$D$2:$D$495)/SUMPRODUCT(--($E$2:$E$49550000),--($E$2:$E$495<75001)))

I would like to add state criteria to this, such as all properties in CA.
How can I accomplish this?

I'd greatly appreciate any suggestions!!!
Thanks
Suzanne

Hi

I assume that you have states in column A, so I just added another condition
in the Sumproduct formulas. "CA" can be substitutet with a cell reference:

=SUMPRODUCT(--($E$2:$E$49550000),--($E$2:$E$495<75001),--($A$2:$A$495="CA"),$D$2:$D$495)/SUMPRODUCT(--($E$2:$E$49550000),--($E$2:$E$495<75001),--($A$2:$A$495="CA"))

Regards,
Per

"SBecker" skrev i meddelelsen
...
I have a formula that gives me the average sales price for properties that
fall within a certain value. I would like to break it down further by
designating these same averages by state rather than just overall. Here
is
my existing formula:

=(SUMPRODUCT(--($E$2:$E$49550000),--($E$2:$E$495<75001),$D$2:$D$495)/SUMPRODUCT(--($E$2:$E$49550000),--($E$2:$E$495<75001)))

I would like to add state criteria to this, such as all properties in CA.
How can I accomplish this?

I'd greatly appreciate any suggestions!!!
Thanks
Suzanne

Per,

Thanks for your help, that works great.....

Suzanne

"Per Jessen" wrote:

Hi

I assume that you have states in column A, so I just added another condition
in the Sumproduct formulas. "CA" can be substitutet with a cell reference:

=SUMPRODUCT(--($E$2:$E$49550000),--($E$2:$E$495<75001),--($A$2:$A$495="CA"),$D$2:$D$495)/SUMPRODUCT(--($E$2:$E$49550000),--($E$2:$E$495<75001),--($A$2:$A$495="CA"))

Regards,
Per

"SBecker" skrev i meddelelsen
...
I have a formula that gives me the average sales price for properties that
fall within a certain value. I would like to break it down further by
designating these same averages by state rather than just overall. Here
is
my existing formula:

=(SUMPRODUCT(--($E$2:$E$49550000),--($E$2:$E$495<75001),$D$2:$D$495)/SUMPRODUCT(--($E$2:$E$49550000),--($E$2:$E$495<75001)))

I would like to add state criteria to this, such as all properties in CA.
How can I accomplish this?

I'd greatly appreciate any suggestions!!!
Thanks
Suzanne

.