Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add cell in every 7th column
Hello,
How do I write a function that adds the values for the intersection between row 12 and every 7th column (to give a concrete example) . To illustrate: Add the cells G12, N12, U12, AB12, ... Can somebody give me an idea? -- igor |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add cell in every 7th column
Try this
=SUM(IF(MOD(COLUMN(G12:IV12),7)=0,G12:IV12,0)) This is an array and must be entered with CTRL+Shift+Enter. Mike "Igorin" wrote: Hello, How do I write a function that adds the values for the intersection between row 12 and every 7th column (to give a concrete example) . To illustrate: Add the cells G12, N12, U12, AB12, ... Can somebody give me an idea? -- igor |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add cell in every 7th column
=SUMPRODUCT(G12:AB12,--(MOD(COLUMN(G12:AB12),7)=0))
-- David Biddulph "Igorin" wrote in message ... Hello, How do I write a function that adds the values for the intersection between row 12 and every 7th column (to give a concrete example) . To illustrate: Add the cells G12, N12, U12, AB12, ... Can somebody give me an idea? -- igor |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add cell in every 7th column
Mike, thank yo very much for the reply,
I just tried this and it returns a null value. By the way, I did not get the part about using the CTRL+Shift+Enter. I copied the formula and pasted it inside the cell itself and then modified the cell references. What did I do wrong? -- igor "Mike H" wrote: Try this =SUM(IF(MOD(COLUMN(G12:IV12),7)=0,G12:IV12,0)) This is an array and must be entered with CTRL+Shift+Enter. Mike "Igorin" wrote: Hello, How do I write a function that adds the values for the intersection between row 12 and every 7th column (to give a concrete example) . To illustrate: Add the cells G12, N12, U12, AB12, ... Can somebody give me an idea? -- igor |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add cell in every 7th column
Try this:
=SUMPRODUCT(--(MOD(COLUMN(G12:BY12)-COLUMN(G12),7)=0),G12:BY12) Adjust the end of range as needed -- Biff Microsoft Excel MVP "Igorin" wrote in message ... Hello, How do I write a function that adds the values for the intersection between row 12 and every 7th column (to give a concrete example) . To illustrate: Add the cells G12, N12, U12, AB12, ... Can somebody give me an idea? -- igor |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add cell in every 7th column
Thanks for the help, David,
I'm not getting the right amount. It returns the value in the 7th cell but not the sum of all th cells. -- igor "David Biddulph" wrote: =SUMPRODUCT(G12:AB12,--(MOD(COLUMN(G12:AB12),7)=0)) -- David Biddulph "Igorin" wrote in message ... Hello, How do I write a function that adds the values for the intersection between row 12 and every 7th column (to give a concrete example) . To illustrate: Add the cells G12, N12, U12, AB12, ... Can somebody give me an idea? -- igor |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add cell in every 7th column
It works!!!
Thanks a lot, T.! -- igor "T. Valko" wrote: Try this: =SUMPRODUCT(--(MOD(COLUMN(G12:BY12)-COLUMN(G12),7)=0),G12:BY12) Adjust the end of range as needed -- Biff Microsoft Excel MVP "Igorin" wrote in message ... Hello, How do I write a function that adds the values for the intersection between row 12 and every 7th column (to give a concrete example) . To illustrate: Add the cells G12, N12, U12, AB12, ... Can somebody give me an idea? -- igor |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add cell in every 7th column
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Igorin" wrote in message ... It works!!! Thanks a lot, T.! -- igor "T. Valko" wrote: Try this: =SUMPRODUCT(--(MOD(COLUMN(G12:BY12)-COLUMN(G12),7)=0),G12:BY12) Adjust the end of range as needed -- Biff Microsoft Excel MVP "Igorin" wrote in message ... Hello, How do I write a function that adds the values for the intersection between row 12 and every 7th column (to give a concrete example) . To illustrate: Add the cells G12, N12, U12, AB12, ... Can somebody give me an idea? -- igor |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add cell in every 7th column
The formula works for me. It gives me the same as =G12+N12+U12+AB12
Perhaps you need to check that you really have numbers in N12, U12, and AB12, and not text? Make sure that there are no spaces or non-printing characters. What do =ISNUMBER(N12) and =ISTEXT(N12) give? -- David Biddulph "Igorin" wrote in message ... Thanks for the help, David, I'm not getting the right amount. It returns the value in the 7th cell but not the sum of all th cells. -- igor "David Biddulph" wrote: =SUMPRODUCT(G12:AB12,--(MOD(COLUMN(G12:AB12),7)=0)) -- David Biddulph "Igorin" wrote in message ... Hello, How do I write a function that adds the values for the intersection between row 12 and every 7th column (to give a concrete example) . To illustrate: Add the cells G12, N12, U12, AB12, ... Can somebody give me an idea? -- igor |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add cell in every 7th column
Note also that my formula is effectively the same as Biff's which you say
works for you. He had: =SUMPRODUCT(--(MOD(COLUMN(G12:BY12)-COLUMN(G12),7)=0),G12:BY12) COLUMN(G12) is 7, so that won't affect the MOD result. The only significant difference is that his range goes on beyond AB12 to BY12, but of course my formula can be extended to cover whatever range is appropriate. If there are cells such as N12, U12 and AB12 which you say are being picked up by Biff';s formula but not by mine, I would be very intrigued to know why. -- David Biddulph "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... The formula works for me. It gives me the same as =G12+N12+U12+AB12 Perhaps you need to check that you really have numbers in N12, U12, and AB12, and not text? Make sure that there are no spaces or non-printing characters. What do =ISNUMBER(N12) and =ISTEXT(N12) give? -- David Biddulph "Igorin" wrote in message ... Thanks for the help, David, I'm not getting the right amount. It returns the value in the 7th cell but not the sum of all th cells. -- igor "David Biddulph" wrote: =SUMPRODUCT(G12:AB12,--(MOD(COLUMN(G12:AB12),7)=0)) -- David Biddulph "Igorin" wrote in message ... Hello, How do I write a function that adds the values for the intersection between row 12 and every 7th column (to give a concrete example) . To illustrate: Add the cells G12, N12, U12, AB12, ... Can somebody give me an idea? -- igor |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maddening Dilemma - Compare each cell within column a to each cell in column b and find unique matches | Excel Worksheet Functions | |||
Sum Cell Values of one column based on Another Cell Value in a different column | Excel Worksheet Functions | |||
compare cells in column to criteria, then average next column cell | Excel Worksheet Functions | |||
Need Formula or macro. If i enter today date in the cell (Row 1,Column 2) and on tab out, the column 1 cell should be filled with "corresponding Day" of the date | Excel Discussion (Misc queries) | |||
Format cell in column B based on value in the next cell (column c) | Excel Discussion (Misc queries) |