SumIf across columns instead of rows

  • Thread starter Thread starter Heather
  • Start date Start date
H

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.
 
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.
 
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)
 
Back
Top