SumIf across columns instead of rows

[Heather]

to I have a set of metrics that are laid out horizontally in a spreadsheet,
and I need to sum up every other column. Is there a way to use SumIf or a
similar formula so I don't have type each cell into a Sum function? Currently
my formula is
=SUM(B6+D6+F6+H6+J6+L6+N6+P6+R6+T6+V6+X6+Z6+AB6+AD6+AF6+AH6+AJ6+AL6+AN6+AP6)
which is very error prone and manual to update when I add a column. Each
column to be added has a column header of "Total" so I tried
=sumif(B5:AQ5,"TOTAL",B6:AQ6) but it returns zero. I also tried
sumif(A:AQ,mod(column(),2)=0,B6:AQ6) and got zero also.

 

[Dave Peterson]

If the values that you're trying to add are really text, then using + will make
excel treat them like real numbers.

So what does:
=SUM(B6,D6,F6,H6,J6,L6,N6,P6,R6,T6,V6,X6,Z6,AB6,AD6,AF6,AH6,AJ6,AL6,AN6,AP6)
Return.

If this returns 0, then those values maybe text (or just coincidentally sum to
0???).

I'd try reformatting each cell as General
then reenter the values (hit F2, then enter will be enough)

You could also select an empty cell
edit|copy
then select the range to fix
edit|paste special|check add and values

====
Just an aside:

I like this formula that you suggested:
=sumif(B5:AQ5,"TOTAL",B6:AQ6)
And if you have total as part of the header, you could use:
=sumif(B5:AQ5,"*TOTAL*",B6:AQ6)

=sumif() will work nicely with wildcards.

 

[Shane Devenshire]

Hi,

This adds every other column starting with B

=SUMPRODUCT(--(MOD(COLUMN(B6:K6),2)=0),B6:K6)

This adds every other column starting with C

=SUMPRODUCT(--(MOD(COLUMN(B6:K6),2)=1),B6:K6)