Automate sum of every second cell in same row.

[dexcel99]

Hi guys,

Please see the attached photo.
Screen Shot 2017-03-16 at 10.37.26 pm.png

In row 2, I will be constantly entering figures in every second cell (B2, D2, F2 etc.). In cell B5 will be where the total of those figures will be. Cells C2, E2, G2 etc. will have values that won't be part of the calculation. I am seeking for a formula that will AUTOMATE the sum of the cells B2, D2, F2, H2, J2 etc.

Thanks

[isskint]

i might be missing something here, but surely =SUM(B2, D2, F2, H2, J2) or =SUM(B2:J2) would work? Even B2+D2+F2...


Ah, think i see what i was missing. Will you be constantly adding to the row over time?

So just SUM(2:2)

[dexcel99]

Sorry. I forgot to mention that cells C3, E2, G2 etc will have a value that won't be part of the calculation.

Thanks though

[k64]

Try this:

Please Login or Register  to view this content.

[dexcel99]

Try this:

Please Login or Register  to view this content.

Thanks. It worked. Is it possible to make the range the entire second row, so whenever I add data in the seoond row, I won't have to change the formula?

[Zer0Cool]

Thanks. It worked. Is it possible to make the range the entire second row, so whenever I add data in the second row, I won't have to change the formula?

You are generally better off dynamically referencing your data instead of using whole row/column/sheet references. You could accomplish this several ways. A table and structured references may work, a dynamic named range, or even a static named range with a pretty high ceiling for the number of columns. All these would limit the number of calculations the formula(s) need to do over blank cells if any.

Hope this helps

Edit: Will the row above your values always have a date above the ones you want to sum and nothing or a non date value above the values you do not wish to sum?

[Zer0Cool]

See attached for my example including dynamic named range

formula on sheet to SUM

Please Login or Register  to view this content.

Named range formula (ValuesRng)

Please Login or Register  to view this content.

*Note the ISBLANK part...if you have continuous data going across until you make new entries then this could be simplified.

If you have continuous data from B2 to the right (not leaving blanks between even columns):
Replace (the whole last argument of OFFSET):

Please Login or Register  to view this content.

with:

Please Login or Register  to view this content.

You could of course adjust the columns threshold by changing "V2" to whatever column off to the right you choose. I would encourage you to not extend it to the last column. If you feel like you might have 500 columns, make it 600, 1000, etc. Something high enough you will rarely if ever need to update but not so high we make the formula look at a ton of empty columns you will never reasonably use.

Hope this helps.

[k64]

You can do the whole row, it will just slow down the formula. You can also do the whole row if you use SUMIFS, but you'll need to add a helper row with something like "=MOD(COLUMN(),2)". You can also do the dynamic range thing or just put a range larger than you'll ever need.

[vlady]

YTD on year?Month? or just sum which ignores blank.

[FlameRetired]

Another way.

This dynamic named range formula.

Please Login or Register  to view this content.

You can generally get away referencing whole columns and row with MATCH when return type is approximate.

Then the formula

Please Login or Register  to view this content.

[k64]

You suggested to use the following formula:
=SUMPRODUCT(B2:H2*(MOD(COLUMN(B2:H2),2)=0))

Could you please explain how the values of c2, e2, g2 etc. (every second cell) are not affecting the formula? Also what effect does equating the formula to zero have?
I understand what the functions do individually.

Here are the parts of the formula:
SUMPRODUCT - Perform multiplication, then add up the results
B2:H2 - The numbers you want to SUM (every other)
MOD - Remainder when divided by a number
COLUMN - Column number of a cell (A=1, B=2...)
*(A=B) - Logical expressions evaluate to TRUE=1 or FALSE=0.

In English, this says: For every cell in B2:H2, if it is in an even column (MOD(COLUMN,2)=0) then multiply it by 1, otherwise multiply it by 0. Add up the results.