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" 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
>
>

[LACA]

Brilliant.

Thank you!

[LACA]

JG

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