Adding alternate columns

  • Thread starter Thread starter LACA
  • Start date Start date
L

LACA

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?
 
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
 
Back
Top