SUMIF help and another query

[SM]

I'm wondering if someone could help with a problem I am having - not so much
a problem - more I'm not as good at Excel as others here and they may be
able to help.

I'm treasurer of a semi-professional non-league football club. As such I
have to calculate travelling expenses for each player. The tax man taxes
players on home games and training sessions but doesn't tax the travelling
expenses for away games for those players that take their own cars and not
the bus. Currently we pay them 15p per mile plus an additional 3p per mile
for every passenger - I know, not much - but we are on a very limited
budget.

I have the following columns
A - Date
B - Name of player
C - Passengers
D - Miles

There are columns for other things such as training session, home game, away
game but it is just the first four columns I am concerned with just now.

Because of the amount paid out in travelling expenses (and the ridiculous
mileage claimed by some players) I am considering scrapping the 3p per
additional passenger amount. I currently use the SUMIF formula to see how
much in total each player has received to date in expenses for training
sessions, home games and away games. I was wondering if someone could find a
formula to do the following :

For each individual find out the total they would lose out on if they
weren't paid this 3p per extra passenger amount. I currently have rows for
each player at the end of the document where this total could go (in any
column from C - K). If this is not possible is it possible to have a total
amount where if the amount in column C > 0 then sum them all up for C x D x
£0.03 . (I know it will be hard to believe after reading that last sentence
but English IS my first language).

Hopefully I wouldn't need an extra column as the space is limited on the
page.

Thanks in advance if anyone an help me.

 

[Roger Govier]

Hi

One way would be to use a Pivot Table.
Insert>Name>Define>Name myData Refers to
=$A$1:INDEX($A:$A,COUNTA($A:$A))

Data>Pivot Table>Next>source =myData >Layout>Drag Name to Row area>Drag
Date to Row area>Drag Mileage to Data area>Double click on Mileage and
ensure it is set to SUM>Finish
On the resulting Pivot Table (created on a new sheet), from the Dropdown on
the Pivot Table Toolbar choose Formulas>Calculated Field
In Field type Base Pay>In Formula type =Mileage*0.15>Add
In the next field use title Supplement>in Formula type
=Mileage*(Passengers*0.03)>Add
in the next field use Title Total Pay>in formula type
=Mileage*(0.15+(Passengers*0.03))>Add>OK

On the Pivot Table drag Data button, and drop on Total, aand you will see
the three sets of figures side by side.

 

[SM]

Too complicated for me as it was my first go at pivot tables. I did some
fiddling about and can see how pivot tables will help me in future. So
thanks for pointing me in that direction.

I went down the road of adding an extra column and it looks like the saving
will be larger than I first thought - probably pay for half a season's wages
for another player.