SUMPRODUCT - How to add multiple ranges in array

**ihaque** · 04-21-2016, 04:29 PM

Hi this may be an easy question but regarding the SUMPRODUCT function - I am trying to get a weighted average.

The issue is in my array 1 part of the equation, I place a range of a column within in it, but lets say I want to omit certain cells within that range (e.g as oppose to A1:A5, I'd prefer - A1:A2,A4:A5)? How do I place that in one array as oppose to the SUMPRODUCT thinking its two arrays?

**ihaque** · 04-21-2016, 04:33 PM

I'd like to note all my arrays contain equal number of columns

**gak67** · 04-21-2016, 04:34 PM

It depends on the reason why you are excluding A3. If it was because it was too large, like you were wanting to exclude any number greater than 10 you would write it like this:
=SUMPRODUCT((A1:A5)*(A1:A5<10))
This would multiply the values in A1:A5 by 1 if they are less than 10 and by 0 if they are more then 10, then sum them. Since anything multiplied by 0 is 0 those items would be effectively excluded from the sum.

**Tony Valko** · 04-21-2016, 04:37 PM

You would try to write an expression as to why you want to exclude A3 or why you want to include A1:A2, A4:A5.

A1 = 12
A2 = 22
A3 = 7
A4 = 15
A5 = 20

=SUMPRODUCT(--(A1:A5>10)...

A3 is excluded because it's value is 7.

**ihaque** · 04-21-2016, 04:40 PM

Okay I'm understanding but lets say I want to carry this formula over the row BUT lets now say A3 - Z3 contains values as 1,2,3,...,n would I then require some form of an IF function I guess?

**gak67** · 04-21-2016, 04:43 PM

Not necessarily. If you could upload a sample of the file you are working on (with any confidential data removed) with what you are trying to acheive clearly shown, we could give you more specific advice.

**ihaque** · 04-21-2016, 05:01 PM

It's 2 MB and there's way too much going on with each sheet linked to another so I don't knwo if I should delete anything and send it... Hmmm here's the formula I'm using based on the advice above (I made the cell I want to omit as 0 for now):

=SUMPRODUCT(((T28:T49)*(T28:T49>0))/(($B$72:$B$90)*($B$72:$B$90>0)),(($C$72:$C$90)*($C$72:$C$90>0)))/SUM(T28:T49/$B$72:$B$90)

Essentially, I'm trying to get the weighted average from the above formula but I'm getting a DIV0 error including in cells where it is populated >0 value. Not sure why unless I wrote the condition wrong?

**kevawo** · 04-21-2016, 05:27 PM

I think what you're trying to do, is what I'm trying to figure out as well.

I want four different TOTALS for the four different columns that repeats itself indefinitely. Out-Ez/Out-Pink/In-Ez/In-Pink

I don't know what I'm doing wrong but I'm trying to add all the "Out-Ez" from Rows: H20 and H35 while omitting totals from the other columns. I haven't gotten to the H35 Row yet because I'm still trying to figure out how to add ROW H20 first.

=SUMPRODUCT(--(MOD((COLUMN(H20:FM20)),7)=0),H20:FM20)

This Function isn't working....

4212016_41524 PM.jpg

**ihaque** · 04-21-2016, 06:08 PM

I ended up creating a separate spreadsheet linking to my orginal data set but deleted cells I did not need THEN used the SUMPRODUCT function and then called out that value back into my original spreadsheet.