software

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 quot; B4 D4 F4quot;, and
the Total Revenue formula is quot; C4 E4 G4quot; 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: www.excelforum.com/member.php...oamp;userid=30381
View this thread: www.excelforum.com/showthread...hreadid=501288Not sure what your column headers are but let's assume that one is quot;Ticketsquot;
and the other quot;Revenuequot; then the formula would be:

=SUMPRODUCT(--($A$1:G$1=quot;ticketsquot;),$A2:G2)
=SUMPRODUCT(--($A$1:G$1=quot;revenuequot;,$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

quot;LACAquot; wrote:

gt;
gt; I work on a schedule where I record ticket count and ticket sales for
gt; each show, listed chronologically.
gt;
gt; Column A = week ending date
gt;
gt; Column B = # Tickets sold for April 5 show
gt; Column C = Ticket Revenue for April 5 show
gt;
gt; Column D = # Tickets sold for April 9 show
gt; Column E = Ticket Revenue for April 9 show
gt;
gt; Column F = # Tickets sold for April 12 show
gt; Column G = Ticket Revenue for April 12 show
gt;
gt; Column H = Total # Tickets sold for all shows
gt; Column I = Total Ticket Revenue for all shows
gt;
gt; In the Totals columns, the Total Tickets formula is quot; B4 D4 F4quot;, and
gt; the Total Revenue formula is quot; C4 E4 G4quot; and so on for each row....
gt;
gt; When I get a new show that occurs chronologically in between two
gt; existing shows, I insert two columns in the appropriate place and then
gt; input my new data. Every time I do this, however, I have to adjust my
gt; Totals formulas to include those new columns (and so each time a show
gt; is added, the Totals formulas get longer).
gt;
gt; Is there a formula I can substitute in my Totals columns that will
gt; automatically include new columns?
gt;
gt;
gt; --
gt; LACA
gt; ------------------------------------------------------------------------
gt; LACA's Profile: www.excelforum.com/member.php...oamp;userid=30381
gt; View this thread: www.excelforum.com/showthread...hreadid=501288
gt;
gt;


Brilliant.

Thank you!--
LACA
------------------------------------------------------------------------
LACA's Profile: www.excelforum.com/member.php...oamp;userid=30381
View this thread: www.excelforum.com/showthread...hreadid=501288
JG

Would you mind explaining the role/purpose of the quot;--quot; in your formula?--
LACA
------------------------------------------------------------------------
LACA's Profile: www.excelforum.com/member.php...oamp;userid=30381
View this thread: www.excelforum.com/showthread...hreadid=501288