sum product * weighted average

marstonk

Active Member
Joined
Jan 30, 2003
Messages
273
=SUMPRODUCT(--(Development!$H$4:$H$42=A5),--(Development!$J$4:$J$42>0),Development!$P$4:$P$42)

THis is my formula but rather than adding them together I need the weighted average of the answer based on another array G:G if the above conditions are matched?

Does this make sense?

Kitty g :rolleyes:
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Do you mean...

=SUMPRODUCT(--(Development!$H$4:$H$42=A5),--(Development!$J$4:$J$42>0),Development!$P$4:$P$42)/SUM(Development!$G$2:$G$42)

or

=SUMPRODUCT(--(Development!$H$4:$H$42=A5),--(Development!$J$4:$J$42>0),Development!$P$4:$P$42)/SUMPRODUCT(--(Development!$H$4:$H$42=A5),--(Development!$J$4:$J$42>0),Development!$G$2:$G$42)

PS. I'm still a bit in dark regarding your "compounding query" in your previous post.
 
Upvote 0

marstonk

Active Member
Joined
Jan 30, 2003
Messages
273
Right I am on the right lines however - I need it to count the number of times the conditions are met and use this to divide by rather than it summing them.

thanks aladin.

my query yesterday has been resolved by a new approach ie what i am trying to do today. my question was about uplifting a value each year over thirty years by 2% so I guess 2^30 for the 30th year? But it didnt work anyhow. So I have changed my approach.

thanks
kitty
 
Upvote 0

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
hi!
Just remove the last argument!
Code:
=SUMPRODUCT(--(Development!$H$4:$H$42=A5),--(Development!$J$4:$J$42>0))
 
Upvote 0

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
marstonk said:
Right I am on the right lines however - I need it to count the number of times the conditions are met and use this to divide by rather than it summing them.

Not clear enough I'm afraid. The numerator is a multiconditional sum. I thought you wanted to incorporate the G range in some way. But what is in G?

my query yesterday has been resolved by a new approach ie what i am trying to do today. my question was about uplifting a value each year over thirty years by 2% so I guess 2^30 for the 30th year? But it didnt work anyhow. So I have changed my approach...

That's great. I'm curious about the solution though.
 
Upvote 0

marstonk

Active Member
Joined
Jan 30, 2003
Messages
273
=COUNTA((SUMPRODUCT((Development!$H$4:$H$42='SO Rents'!A5),(Development!$K$4:$K$42='SO Rents'!$F$5))),Development!$P$4:$P$42)

Hi this is what I need to do count how many times a value appears in column P when the criterea is met. Does this make sense? It is returning the same value for each row so it doesnt work any recommendations on how to get it to count the times a value occurs when the condition is met?

Thanks

struggling along kitty g
:pray:
 
Upvote 0

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
marstonk said:
=COUNTA((SUMPRODUCT((Development!$H$4:$H$42='SO Rents'!A5),(Development!$K$4:$K$42='SO Rents'!$F$5))),Development!$P$4:$P$42)

Hi this is what I need to do count how many times a value appears in column P when the criterea is met. Does this make sense? It is returning the same value for each row so it doesnt work any recommendations on how to get it to count the times a value occurs when the condition is met?

Thanks

struggling along kitty g
:pray:

Kitty,

=SUMPRODUCT(--(Development!$H$4:$H$42='SO Rents'!A5),--(Development!$K$4:$K$42='SO Rents'!$F$5),Development!$P$4:$P$42)

gives you a multiconditional sum, that is, every value from $P$4:$P$42 is added up when $H$4:$H$42 is A5 and $K$4:$K$42 is F5.

=SUMPRODUCT(--(Development!$H$4:$H$42='SO Rents'!A5),--(Development!$K$4:$K$42='SO Rents'!$F$5))

gives you a multiconditional count, that is, 1 when when $H$4:$H$42 is A5 and $K$4:$K$42 is F5. And all ones are summed up. Summing a bunch of individual 1's is identical to counting those 1's.

BTW, you must not omit the -- bit, which converts the truth values (TRUE/FALSE) into numbers (1/0).
 
Upvote 0

Forum statistics

Threads
1,186,361
Messages
5,957,402
Members
438,304
Latest member
duck90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top