Excel formulas

[scotyboy56]

Hoping someone can help, I am creating a pupil premium tracking spreadsheet for costs, for one of my Primary schools, I have finished it and everything, just wanted to add one extra thing in which adds total cost spent on child.

What I'm wanting is to see if the cell contains an x, if it does then take the cost per pupil for that support, and add them all up for that pupil to see how much is being spent per child.

 

Hope that makes sense, I have attached my spreadsheet so you can see what I'm actually on about

Pupil Premium Tracking2.xlsx

[Seb1780]

What I would do is have three tables (split over multiple sheets if you prefer).

 

A table showing which pupils received which support, but instead of using an "x" to identify them use a number; this method allows for multiple doses of the same support.

 

A table showing the cost of each support option

 

A table showing the multiple of cost by number of support episodes per pupil

 

By using absolute cell references to reference the cost (e.g. $A$2) you only need to build the first line and them copy it for each subsequent pupil.

[LosOjos]

Agree with @Seb1780 - it's always a good idea to separate data entry from presentation.

 

However, for simplicity's sake I have attached a couple of examples for you.

 

The SUMIF version simply looks for an 'x' and adds that support type's cost to a running total. If all support types cost the same, you could replace it with a COUNTIF and multiply the result by the cost to get your total.

 

The SUMPRODUCT version looks for numerical values, so it's possible to enter multiple instances of each support type.

Pupil Premium Tracking2 SUMPRODUCT.xlsx

Pupil Premium Tracking2 SUMIF.xlsx

[scotyboy56]

Thanks for the advice, I think I know what you are on about(I am useless with excel formulas), I have just done a little test and it seems to work

[scotyboy56]

Agree with @Seb1780 - it's always a good idea to separate data entry from presentation.

 

However, for simplicity's sake I have attached a couple of examples for you.

 

The SUMIF version simply looks for an 'x' and adds that support type's cost to a running total. If all support types cost the same, you could replace it with a COUNTIF and multiply the result by the cost to get your total.

 

The SUMPRODUCT version looks for numerical values, so it's possible to enter multiple instances of each support type.

 

That is perfect!!