# sum product * weighted average

#### marstonk

##### Active Member
=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

### 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.

#### SIXTH SENSE

##### Well-known Member
Hi!

Care to post sample data? You may use colo's html maker!

##### MrExcel MVP
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
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.

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
hi!
Just remove the last argument!
Code:
=SUMPRODUCT(--(Development!\$H\$4:\$H\$42=A5),--(Development!\$J\$4:\$J\$42>0))

#### marstonk

##### Active Member
Sorry it is not a count of the conditions it is the value in column J I want to divid e by.

##### MrExcel MVP
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
=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
ray:

##### MrExcel MVP
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
ray:

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).

Replies
2
Views
355
Replies
3
Views
115
Replies
1
Views
109
Replies
3
Views
60
Replies
1
Views
235

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.

### Which adblocker are you using?

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

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