Adding alternate columns

[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?

 

[pinmaster]

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]

Brilliant.

Thank you!

 

[LACA]

JG

Would you mind explaining the role/purpose of the "--" in your formula?