ticket counting

B

Bob

I have to show how many tickets are disbursed to different people.
Let's say Bob gets tickets 100 to 105. The SUM formula subtracting 100
from 105 is 5 when actually Bob gets 6 tickets. Do i have to add a
hidden column with a value of 1 to insert into the formula? Thanks
 
K

Ken Wright

As long as your data is in database format with a name against every ticket
number, then just throw a Pivot table at your data and just have it count
the rcords.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------
 
G

Gary''s Student

No


Just change the formula a little bit

if A1 contains 100 and A2 contains 105 then the number of ticks is:

=A2-A1+1
 
B

Bob

Thanks Ken.
I won't have access to to individual ticket sales. I just disburse
packets of tickets.
I hope this isn't rude (new to forum), i just need to sum the # of
tickets in packets to the sellers.
Ex:
/ start # / end # / # received
Bob 100 104 5
Joe 105 109 5
Pat 110 114 5

Total tickets disbursed 15

For now i have a hidden column with a value of 1 to add to the SUM
formula to make the formula work. But, unless there is a ticket
starting number i get a #VALUE error in my totals columns and am unable
to see my running total of tickets disbursed. I can't think how to tell
my "# received" column to take ticket 100 to 104 and calculate it as 5
(without that hidden column with a 1 that is).
Thanks for your patience.
Bob
 
S

Sandy Mann

The simple answer to your question is yes, you have to add 1.

If Bob gets ticket number105, then 105 is not being included in the
calculation, (if Bob gets ticket Numbers 1 to 10 then 10-1 is only 9 not
10), to be included, ticket Number 105 must be added in afterwards or use
the *real* subtraction which is 105-99 to give 6.
--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk
 
Top