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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

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
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883

ADVERTISEMENT

hi!
Just remove the last argument!
Code:
=SUMPRODUCT(--(Development!$H$4:$H$42=A5),--(Development!$J$4:$J$42>0))
 

marstonk

Active Member
Joined
Jan 30, 2003
Messages
273
Sorry it is not a count of the conditions it is the value in column J I want to divid :unsure: e by.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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.
 

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:
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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).
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,871
Messages
5,766,855
Members
425,380
Latest member
CubeGirl

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