# SUMPRODUCT with multiple ranges

#### LC1234

##### New Member
Here is the formula as I have it typed in now. It returns a #VALUE! error. The problem is with the 2nd SUMPRODUCT - it doesn't seem to want to use multiple ranges.

=IF(SUMPRODUCT(--(F666:I666>0),F39:I39)>0,SUMPRODUCT((F39:I39,K39:M39,O39:Q39,S39:U39),(F666:I666,K666:M666,O666:Q666,S666:U666))/SUMPRODUCT(--(F666:I666>0),F39:I39),0)

What I want the 2nd SUMPRODUCT to do is F39*F666...I39*I666+K39*K666..M39*M666+O39*O666.........

Tired separating the different "sub"ranges with semicolons, but excel changes them to colons, and then it errors out. I'd rather not use 4 separate SUMPRODUCT formulas, as I'm going to be using this many times in the worksheet.

Suggestions?
Thanks,
Lisa

### 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.
It would be easier if you were skipping every fourth column, but F39:I39 breaks the pattern.

That said this worked for me:

=SUMPRODUCT(--(COLUMN(F39:U39)<>10),--(COLUMN(F39:U39)<>14),--(COLUMN(F39:U39)<>18),F39:U39,F666:U666)

Thanks, that worked!

What would you have suggested if it was skipping every 4th column?

LC1234 said:
Thanks, that worked!

What would you have suggested if it was skipping every 4th column?

Then you could use eg:

=SUMPRODUCT(--(MOD(COLUMN(F39:U39)-COLUMN(F39)+1,4)>0),F39:U39,F666:U666)

Replies
9
Views
757
Replies
3
Views
203
Replies
0
Views
295
Replies
0
Views
124
Replies
1
Views
654

1,207,169
Messages
6,076,908
Members
446,239
Latest member
Home Nest

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