SUMPRODUCT with multiple ranges

[LC1234]

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

 

[Andrew Poulsom]

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)

 

[LC1234]

Thanks, that worked!

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

 

[Andrew Poulsom]

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)