Work out total amount of 30, 60, 90, 91+ day debts

calculate the total amount of debts using date colum

Didn't find your answer?

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.

Routemaster image
By tom123
19th Jun 2017 14:00

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:)

Thanks (0)
Replying to tom123:
avatar
By Pluribus
19th Jun 2017 14:27

Hi Tom

It would be amazing if you could do that for me, thank you :)

As soon as I work out how to PM, I will send the spreadsheet over to you :)

Thanks (0)
By Duggimon
19th Jun 2017 14:25

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!

Thanks (0)