Using If with SumProduct help please!

[SBecker]

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$495>50000),--($E$2:$E$495<75001),$D$2:$D$495)/SUMPRODUCT(--($E$2:$E$495>50000),--($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 Jessen]

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$495>50000),--($E$2:$E$495<75001),--($A$2:$A$495="CA"),$D$2:$D$495)/SUMPRODUCT(--($E$2:$E$495>50000),--($E$2:$E$495<75001),--($A$2:$A$495="CA"))

Regards,
Per

 

[SBecker]

Per,

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

Suzanne