Hi,
we use a formula to get a weighted average.
=SUMPRODUCT(D2:D14,C2:C14)/SUM(D2:D14)
I would like to incorporate some SUMIFs based on some criteria. average by Country as an example.
what formula would I need to enter in cell J2 of the attached example data?
Many thanks in advance
Try:Formula:
=SUMPRODUCT((A$2:A$14=I2),(D$2:D$14),(C$2:C$14))/SUM(D$2:D$14)
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Hi Trevor,
unfortunately, I just get 0 as a result. any other suggestions?
I think this needs to be either
or=SUMPRODUCT((A$2:A$14=I2) * (D$2:D$14) * (C$2:C$14))/SUM(D$2:D$14)
My preference is for the first incantation where * is analogous to AND and + is analogous to OR.=SUMPRODUCT(--(A$2:A$14=I2),--(D$2:D$14),--(C$2:C$14))/SUM(D$2:D$14)
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Im confused....
do you want SUM or average?I would like to incorporate some SUMIFs based on some criteria. average by Country as an example.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
great, thanks Trevor. they both worked.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
milo1984 
Register To Reply
Bookmarks