|
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 06:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com