SUMPRODUCT NON-ADJACENT CELLS

[Guest]

I have a spreadsheet with values in f25,f32,f39....b25, b32,b39. I want to
evaluate the sumproduct of these two arrays. I have tried
sumproduct((f25,f32,f39),(b25,b32,b39)) and it returns the #VALUE! error. I
have tried naming these ranges and various other syntax, but I have had no
luck. Can you calculate a sum product on non-adjacent cells?

 

[Peo Sjoblom]

If it has a pattern like you seem to have (every 7th cell)


=SUMPRODUCT(--(MOD(ROW(B25:B39),7)=4),--(B25:B39),--(MOD(ROW(F25:F39),7)=4),--(F25:F39))


will work if the values start in row 25, if it starts in row 1 you can use

MOD(ROW(B1:B100),7)=1


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[Guest]

I gave an example that was too easy apparently! On the particular
spreadsheet I am on that will work fine, but I have a couple other where
there is no rhyme or reason to the row number. Is there a way to handle this
if it was f25,f31,f34...b25,b31,b34?

If not, thanks for the help that you have given me!