Weighted Average with multiple conditions

[edethington]

I am trying to create a weighted average based on multiple conditions
in a spreadsheet and can't quite seem to get the formula right. For
Example, my data is as follows

Client Region Year Sales Hourly Rate
Client 1 North 2008 1000000 85
Client 2 South 2008 1500000 90
Client 3 East 2007 300000 110
Client 4 North 2007 500000 105
Client 5 East 2006 3500000 98
Client 6 West 2006 150000 100
Client 7 North 2008 5000000 107
Client 8 South 2007 200000 100

From this data, I would like to calculate the weighted average hourly
rate for sales in each region for each year. So in a particular cell
I would want the weighted average of hours for customers in the north
region for 2008 which would end up being clients 1 and 7. I have
previously used sumproduct() for weighted averages, but never with
criteria, especially 2 different ones and I can't quite seem to get it
to work.

 

[Bernard Liengme]

This
=SUMPRODUCT(--($B$2:$B$9="North"),--($C$2:$C$9=2008),$D$2:$D$9,$E$2:$E$9)/SUMPRODUCT(--($B$2:$B$9="North"),--($C$2:$C$9=2008),$D$2:$D$9)

selects the North region in 2008 and for them it computes (Sales*Hourly
rate)/Sales
to give 103.33. Is this what is needed?

The "North" and 2008 could be replaced by cell references
=SUMPRODUCT(--($B$2:$B$9=F1),--($C$2:$C$9=G1),$D$2:$D$9,$E$2:$E$9)
with "North" (without quotes) in F1 and 2008 in G1

For more see:
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes

 

[edethington]

Thanks for your help! Thanks works, but I have one followup
question. With my data I don't know how many rows of data I am going
to have, but I would like to include it up to at least 5000 rows. The
problem is that when I include the blank rows I just get N/A
returned. How can I include the extra rows in my formula, but exclude
them from the calculation if they are blank?

 

[Bernard Liengme]

I tried this with blank rows and all was well.
The error N/A suggest something that should be a number is not
Are they truly blank rows?

By the way do not use full column references such as
SUMPRODUCT(--(B:B="North")
unless you have Excel 2007

If you want to send me a file I would be happy to check it for you. (remove
TRUENORTH. from my email address)