| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding alternate columns
I work on a schedule where I record ticket count and ticket sales for each show, listed chronologically. Column A = week ending date Column B = # Tickets sold for April 5 show Column C = Ticket Revenue for April 5 show Column D = # Tickets sold for April 9 show Column E = Ticket Revenue for April 9 show Column F = # Tickets sold for April 12 show Column G = Ticket Revenue for April 12 show Column H = Total # Tickets sold for all shows Column I = Total Ticket Revenue for all shows In the Totals columns, the Total Tickets formula is "+B4+D4+F4", and the Total Revenue formula is "+C4+E4+G4" and so on for each row.... When I get a new show that occurs chronologically in between two existing shows, I insert two columns in the appropriate place and then input my new data. Every time I do this, however, I have to adjust my Totals formulas to include those new columns (and so each time a show is added, the Totals formulas get longer). Is there a formula I can substitute in my Totals columns that will automatically include new columns? -- LACA ------------------------------------------------------------------------ LACA's Profile: http://www.excelforum.com/member.php...o&userid=30381 View this thread: http://www.excelforum.com/showthread...hreadid=501288 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding alternate columns
Not sure what your column headers are but let's assume that one is "Tickets"
and the other "Revenue" then the formula would be: =SUMPRODUCT(--($A$1:G$1="tickets"),$A2:G2) =SUMPRODUCT(--($A$1:G$1="revenue",$A2:G2) leave the reference to column A (date column) in the formula, that way you can insert columns anywhere between column A and your totals columns without messing up the results. HTH JG "LACA" wrote: I work on a schedule where I record ticket count and ticket sales for each show, listed chronologically. Column A = week ending date Column B = # Tickets sold for April 5 show Column C = Ticket Revenue for April 5 show Column D = # Tickets sold for April 9 show Column E = Ticket Revenue for April 9 show Column F = # Tickets sold for April 12 show Column G = Ticket Revenue for April 12 show Column H = Total # Tickets sold for all shows Column I = Total Ticket Revenue for all shows In the Totals columns, the Total Tickets formula is "+B4+D4+F4", and the Total Revenue formula is "+C4+E4+G4" and so on for each row.... When I get a new show that occurs chronologically in between two existing shows, I insert two columns in the appropriate place and then input my new data. Every time I do this, however, I have to adjust my Totals formulas to include those new columns (and so each time a show is added, the Totals formulas get longer). Is there a formula I can substitute in my Totals columns that will automatically include new columns? -- LACA ------------------------------------------------------------------------ LACA's Profile: http://www.excelforum.com/member.php...o&userid=30381 View this thread: http://www.excelforum.com/showthread...hreadid=501288 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding alternate columns
Brilliant. Thank you! -- LACA ------------------------------------------------------------------------ LACA's Profile: http://www.excelforum.com/member.php...o&userid=30381 View this thread: http://www.excelforum.com/showthread...hreadid=501288 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding alternate columns
JG Would you mind explaining the role/purpose of the "--" in your formula? -- LACA ------------------------------------------------------------------------ LACA's Profile: http://www.excelforum.com/member.php...o&userid=30381 View this thread: http://www.excelforum.com/showthread...hreadid=501288 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Ignore #REF! when adding columns? | Excel Discussion (Misc queries) | |||
| Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
| adding columns | Excel Discussion (Misc queries) | |||
| problem with adding columns of numbers | Excel Worksheet Functions | |||
| Disable Adding or Deleting Rows and Columns | Excel Discussion (Misc queries) | |||
Linear Mode
