Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf across columns instead of rows
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+AD 6+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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf across columns instead of rows
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,AD 6,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. Heather wrote: 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+AD 6+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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf across columns instead of rows
What did you want the SUM function to add to
B6+D6+F6+H6+J6+L6+N6+P6+R6+T6+V6+X6+Z6+AB6+AD6+AF6 +AH6+AJ6+AL6+AN6+AP6 ? If what you wanted was =B6+D6+F6+H6+J6+L6+N6+P6+R6+T6+V6+X6+Z6+AB6+AD6+AF 6+AH6+AJ6+AL6+AN6+AP6 then you don't need the SUM function. If you want to use the SUM function, you could use =SUM(B6,D6,F6,H6,J6,L6,N6,P6,R6,T6,V6,X6,Z6,AB6,AD 6,AF6,AH6,AJ6,AL6,AN6,AP6) -- David Biddulph "Heather" wrote in message ... 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+AD 6+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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf across columns instead of rows
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) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Heather" wrote: 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+AD 6+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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 - change columns to rows and rows to columns | Excel Discussion (Misc queries) | |||
Excel 2003 - change columns to rows and rows to columns | Excel Discussion (Misc queries) | |||
how to interchange rows to columns & columns to rows in a table | Excel Discussion (Misc queries) | |||
SUMIF with rows and columns | Excel Worksheet Functions | |||
sumif columns and rows | Excel Worksheet Functions |