Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT NON-ADJACENT CELLS
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT NON-ADJACENT CELLS
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) "rstanton1" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT NON-ADJACENT CELLS
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! "Peo Sjoblom" wrote: 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) "rstanton1" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
By selecting cells adjacent to cells tally sheet | Excel Worksheet Functions | |||
How do I fill (copy) nonadjacent cells to adjacent cells? | Excel Discussion (Misc queries) | |||
How do I use countif across non-adjacent cells? | Excel Worksheet Functions | |||
Paste to adjacent cells | Excel Discussion (Misc queries) | |||
How can I add non-adjacent cells with a formula | Excel Worksheet Functions |