Hello everyone
Please see the spreadsheet below. I would like to calculate totals (using the values of the column Amount with VAT) for each magazine in the totals columns on the right, where the totals equal 30 days after invoice date, 60, 90 and 91+ days after invoice date.
Invoices are entered as they are generated so a magazine may have multiple entires on the spreadsheet for the same date. These will be different invoices so the total of all the invoices for that magazine need to be calculated, and those that are 30 days late, 60 days etc need to be added together and put into the relevant cell on the right. I hope this makes sense?
I don't have the faintest idea where to start :(
Can anyone please assist me?
Magazine | Amount (With VAT) | Amount (no VAT) | Inv date | |||||||
SH | £474.00 | £395.00 | 24/02/17 | |||||||
SH | £180.00 | £150.00 | 24/02/17 | |||||||
IFB | £562.50 | £562.50 | 13/04/17 | 0-30 days | 31-60 days | 61-90 days | <91 days | |||
IFB | £500.00 | £500.00 | 13/04/17 | IFB | 0.00 | |||||
SB | £495.00 | £495.00 | 18/04/17 | ITM | 0.00 | |||||
SB | £1,650.00 | £1,650.00 | 18/04/17 | MAV | 0.00 | |||||
SB | £1,999.00 | £1,999.00 | 18/04/17 | SB | 0.00 | |||||
SB | £1,990.00 | £1,990.00 | 18/04/17 | SH | 0.00 | |||||
SB ONLINE | £796.58 | £796.58 | 28/04/17 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | ||
SB ONLINE | £1,593.16 | £1,593.16 | 28/04/17 | |||||||
SB ONLINE | £1,201.48 | £1,201.48 | 18/05/17 | |||||||
SB ONLINE | £1,180.47 | £1,180.47 | 18/05/17 | |||||||
SB ONLINE | £1,201.48 | £1,201.48 | 18/05/17 | |||||||
SB ONLINE | £219.02 | £219.02 | 18/05/17 | |||||||
SB ONLINE | £1,375.00 | £1,375.00 | 18/05/17 | |||||||
SB ONLINE | £211.96 | £211.96 | 18/05/17 | |||||||
SB ONLINE | £219.02 | £219.02 | 18/05/17 | |||||||
SB ONLINE | £651.48 | £651.48 | 18/05/17 | |||||||
SB ONLINE | £630.47 | £630.47 | 18/05/17 | |||||||
SB ONLINE | £651.48 | £651.48 | 18/05/17 | |||||||
SB ONLINE | £7,080.00 | £5,900.00 | 11/06/17 | |||||||
SB ONLINE | £2,340.00 | £2,340.00 | 11/06/17 | |||||||
MAV | £450.00 | £450.00 | 18/04/17 | |||||||
MAV | £395.00 | £395.00 | 18/04/17 | |||||||
MAV | £600.00 | £500.00 | 18/04/17 | |||||||
MAV | £300.00 | £200.00 | 18/04/17 | |||||||
MAV | £200.00 | £200.00 | 18/04/17 | |||||||
MAV | £240.00 | £200.00 | 18/04/17 | |||||||
MAV | £851.04 | £709.20 | 18/04/17 | |||||||
MAV | £750.00 | £750.00 | 18/04/17 | |||||||
MAV | £2,040.00 | £1,700.00 | 18/04/17 | |||||||
MAV | £600.00 | £500.00 | 18/05/17 | |||||||
SB | £5,100 | £4,250 | ||||||||
SB | £5,100 | £4,250 | ||||||||
SB | £5,100 | £4,250 | ||||||||
SB | £4,250 | £4,250 | ||||||||
SB | £4,250 | £4,250 | ||||||||
SB | £4,250 | £4,250 | ||||||||
SH | £900 | £750 | 08/05/17 | |||||||
SH | £2,160 | £1,800 | 08/05/17 | |||||||
SH | £120 | £100 | 08/05/17 | |||||||
SH | £1,074 | £895 | 08/05/17 | |||||||
SH | £540 | £450 | 08/05/17 | |||||||
SH | £144 | £120 | 08/05/17 | |||||||
SH | £108 | £90 | 08/05/17 | |||||||
ITM | £1,450 | £1,450 | 27/04/17 | |||||||
ITM | £2,940 | £2,450 | 27/04/17 | |||||||
ITM | £600 | £500 | 27/04/17 | |||||||
ITM | £1,000 | £1,000 | 11/06/17 | |||||||
IC | £600 | £500 | 18/05/17 | |||||||
IC | £820 | £820 | 18/05/17 | |||||||
MAV | £750.00 | £750.00 | 16/05/17 | |||||||
MAV | £414.00 | £345.00 | 16/05/17 | |||||||
MAV | £350.00 | £350.00 | 25/05/17 | |||||||
MAV | £1,080.00 | £900.00 | 25/05/17 | |||||||
MAV | £540.00 | £450.00 | 25/05/17 | |||||||
MAV | £270.00 | £225.00 | 25/05/17 | |||||||
MAV | £420.00 | £350.00 | 25/05/17 | |||||||
MAV | £186.30 | £155.25 | 25/05/17 | |||||||
MAV | £690.00 | £575.00 | 25/05/17 | |||||||
MAV | £600.00 | £500.00 | 25/05/17 | |||||||
MAV | £354.00 | £295.00 | 25/05/17 | |||||||
MAV | £480.00 | £400.00 | 25/05/17 | |||||||
MAV | £360.00 | £300.00 | 25/05/17 | |||||||
MAV | £66.00 | £55.00 | 25/05/17 | |||||||
MT | £900.00 | £750.00 | 25/05/17 | |||||||
MT | £6,000.00 | £5,000.00 | 11/06/17 | |||||||
MT | £3,000.00 | £3,000.00 | 14/06/17 | |||||||
SMP | £1,100.00 | £1,100.00 | 06/06/17 | |||||||
SMP | £1,344.00 | £1,120.00 | 06/06/17 | |||||||
SMP | £210.00 | £175.00 | 06/06/17 | |||||||
SMP | £3,600.00 | £3,600 | 08/06/17 | |||||||
SMP | £1,750 | £1,750 | 14/06/17 | |||||||
IFB | £900.00 | £900.00 | 06/06/17 | |||||||
IFB | £3,000.00 | £3,000.00 | 06/06/17 | |||||||
IFB | £1,140.00 | £950.00 | 06/06/17 | |||||||
IFB | £600.00 | £500.00 | 06/06/17 | |||||||
IFB | £1,000.00 | £1,000.00 | 06/06/17 | |||||||
IFB | £1,350.00 | £1,125.00 | 06/06/17 | |||||||
IFB | £2,500.00 | £2,500.00 | 06/06/17 | |||||||
IFB | £900.00 | £900.00 | 06/06/17 | |||||||
IFB | £675.00 | £675.00 | 06/06/17 | |||||||
IFB | £1,200.00 | £1,200.00 | 06/06/17 | |||||||
IFB | £2,133.00 | £2,133.00 | 06/06/17 | |||||||
IFB | £1,000.00 | £1,000.00 | 06/06/17 | |||||||
IFB | £720.00 | £600.00 | 06/06/17 | |||||||
IFB | £675.00 | £562.50 | 06/06/17 | |||||||
IFB | £1,500.00 | £1,500.00 | 06/06/17 | |||||||
IFB | £1,998.33 | £1,998.33 | 06/06/17 | |||||||
IFB | £950.00 | £950.00 | 06/06/17 | |||||||
SB | £1,250.00 | £1,250.00 | 11/06/17 | |||||||
£122,089.77 | £112,831.38 |
Replies (3)
Please login or register to join the discussion.
If you don't have software that can do this for you (ie accounting software aged debtors reports) then I would suggest that pivot tables are the way to go.
Send me the data on a PM and I might even do it for you:)
You can do it simply by adding a column for each date range you want then putting an IF statement in there. IF date in date column is less than x and greater than y then use gross, else zero. Then the column totals give you the totals for those date ranges.
If you can use pivot tables then even better and if you can sweet talk Tom into doing it for you then that's better still!