Automate sum of every second cell in same row.

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

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)

Try this:

Formula:

=SUMPRODUCT(B2:H2*(MOD(COLUMN(B2:H2),2)=0))

Originally Posted by k64

Try this:

Formula:

=SUMPRODUCT(B2:H2*(MOD(COLUMN(B2:H2),2)=0))

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?

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.

See attached for my example including dynamic named range

formula on sheet to SUM

Formula:

=SUMPRODUCT(ValuesRng*ISEVEN(COLUMN(ValuesRng)))

Named range formula (ValuesRng)

Formula:

=OFFSET(Sheet1!$B$2,0,0,1,MAX(COLUMN(Sheet1!$B$2:$V$2)*NOT(ISBLANK(Sheet1!$B$2:$V$2))))

*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):

Formula:

MAX(COLUMN(Sheet1!$B$2:$V$2)*NOT(ISBLANK(Sheet1!$B$2:$V$2)))

with:

Formula:

COUNTA(Sheet1!$B$2:$V$2)

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.

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

Another way.

This dynamic named range formula.

Formula:

=Sheet1!$B$2:INDEX(Sheet1!$2:$2,MATCH(1E+306,Sheet1!$2:$2,1))

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

Then the formula

Formula:

=SUM(INDEX(DynaRng,0))

Originally Posted by dexcel99

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.