I want to SUM values in alternate colums B3 D3 F3 H3 J3 L3 N3 P3 till AL3
using some formula which automaticaly identifies alternate colums and add the
values.

cannot use " =B3+D3+F3+H3 "

Try this

=SUM(IF(MOD(B3:AL3,2)=1,B3:AL3))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"Danish Ayub" wrote:

I want to SUM values in alternate colums B3 D3 F3 H3 J3 L3 N3 P3 till AL3
using some formula which automaticaly identifies alternate colums and add the
values.

cannot use " =B3+D3+F3+H3 "

=SUM(IF(MOD(B3:AL3,2)=0,B3:AL3)) << Note req'd chg to "=0"

"Mike H" wrote:

Try this

=SUM(IF(MOD(B3:AL3,2)=1,B3:AL3))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"Danish Ayub" wrote:

I want to SUM values in alternate colums B3 D3 F3 H3 J3 L3 N3 P3 till AL3
using some formula which automaticaly identifies alternate colums and add the
values.

cannot use " =B3+D3+F3+H3 "

try this ( use ctrl + shift + enter )

=SUM(IF(MOD(COLUMN(B3:AL3),2)=0,B3:AL3))


On Nov 20, 5:10*pm, Danish Ayub
wrote:
I want to SUM values in alternate colums B3 D3 F3 H3 J3 L3 N3 P3 till AL3
using some formula which automaticaly identifies alternate colums and add the
values.

cannot use " =B3+D3+F3+H3 "

Hi,

The real error in the formula is the missing 'Column'

=SUM(IF(MOD(COLUMN(B3:AL3),2)=0,B3:AL3))

Mike

"JMay" wrote:

=SUM(IF(MOD(B3:AL3,2)=0,B3:AL3)) << Note req'd chg to "=0"

"Mike H" wrote:

Try this

=SUM(IF(MOD(B3:AL3,2)=1,B3:AL3))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"Danish Ayub" wrote:

I want to SUM values in alternate colums B3 D3 F3 H3 J3 L3 N3 P3 till AL3
using some formula which automaticaly identifies alternate colums and add the
values.

cannot use " =B3+D3+F3+H3 "

I'm not sure why you can't use the formula you suggested, but even though the
suggestions based on the column number (mod 2) will work, I wouldn't use it.

If you decide to add a column (for descriptions???) or delete a column or put a
date into one of those columns, you may find that it doesn't give you the
results you want.

I would insert an extra row (and hide it later). I'd put an indicator in that
row for each column that needed to be summed.

Then I could use:
=sumif(1:1,"x",3:3)
(where row 1 is my hidden row with the X's and row 3 is the row I want to sum)

It may seem like more work to add those indicators, but when you share the
workbook with others and they start making changes, you may be happier.



Danish Ayub wrote:

I want to SUM values in alternate colums B3 D3 F3 H3 J3 L3 N3 P3 till AL3
using some formula which automaticaly identifies alternate colums and add the
values.

cannot use " =B3+D3+F3+H3 "

--

Dave Peterson