+ Reply to Thread
Results 1 to 14 of 14

Breaking down a list of supplier invoices

  1. #1
    Registered User
    Join Date
    01-26-2014
    Location
    South Africa
    MS-Off Ver
    Excel 365 (latest version)
    Posts
    29

    Breaking down a list of supplier invoices

    Good day

    Hoping someone can help me.

    I have a list of invoices from Suppliers that is outstanding. Now i have managed to do a breakdown of how much is owed to each supplier but now i would like the next step to be worked out according to how much is owed to each supplier in a certain month.

    I have attached a sample file. First Sheet is outstanding invoices, 2nd sheet is breakdown. I would like to know how i can it to check according to Name, Date and Amount. I have tried SUMPRODUCT, VLOOKUP & TEXT(MMMM) but i don't know how to put into one formula.

    Is it possible someone could assist. It would be greatly appreciated
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,958

    Re: Breaking down a list of supplier invoices

    Cell B4 formula below, copy down and across:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-26-2014
    Location
    South Africa
    MS-Off Ver
    Excel 365 (latest version)
    Posts
    29

    Re: Breaking down a list of supplier invoices

    Thank you so much. IT gives me an error at the Outstanding, For some reason i have to change the commas for ; Now the only thing is that i have copied and pasted and all totals are now zero

  4. #4
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Breaking down a list of supplier invoices

    Breakdown of Suppliers


    B4=SUMPRODUCT((Outstanding!$B$4:$B$52='Breakdown of Suppliers'!$A4)*(MONTH(Outstanding!$A$4:$A$52)=MONTH('Breakdown of Suppliers'!B$3&0))*(Outstanding!$E$4:$E$52))

    Copy across and down

    for hide 0 custom formatting [=0]""

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Breaking down a list of supplier invoices

    Removed by JT
    Last edited by JohnTopley; 08-27-2021 at 08:29 AM.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Breaking down a list of supplier invoices

    Make dates in row 3 as Excel dates e.g 01/01/2021 and format as "mmmm": post #2 formula will work.

  7. #7
    Registered User
    Join Date
    01-26-2014
    Location
    South Africa
    MS-Off Ver
    Excel 365 (latest version)
    Posts
    29

    Re: Breaking down a list of supplier invoices

    Hi there

    Thank you but when i do that all totals are still 0

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Breaking down a list of supplier invoices

    See attached
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-26-2014
    Location
    South Africa
    MS-Off Ver
    Excel 365 (latest version)
    Posts
    29

    Re: Breaking down a list of supplier invoices

    Thank you so much, it worked. Really appreciate your assistance

  10. #10
    Registered User
    Join Date
    01-26-2014
    Location
    South Africa
    MS-Off Ver
    Excel 365 (latest version)
    Posts
    29

    Re: Breaking down a list of supplier invoices

    Changed dates like you said, I had the formula but had no idea n how to put it all together. Thank you so much for all your help

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Breaking down a list of supplier invoices

    Josephteh did the work.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  12. #12
    Registered User
    Join Date
    01-26-2014
    Location
    South Africa
    MS-Off Ver
    Excel 365 (latest version)
    Posts
    29

    Re: Breaking down a list of supplier invoices

    Already Done!!!

    Thanks guys, have a great weekend!!!

  13. #13
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Breaking down a list of supplier invoices

    Post # 4 works ?

  14. #14
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,958

    Re: Breaking down a list of supplier invoices

    Quote Originally Posted by josephteh View Post
    Cell B4 formula below, copy down and across:
    Please Login or Register  to view this content.
    Sorry, forgot to mention I have converted your months to date. Please see attached file.

    Because of your regional setting, you have to change commas to semi-colons.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Multiple invoices and payments from same supplier, data track
    By GEORGE2104 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-13-2021, 01:47 AM
  2. [SOLVED] Formula to return Yes if the Supplier is Xomo and if the Supplier Bisum job title
    By lachonda1 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-06-2020, 06:43 PM
  3. Extract a list of products from a supplier
    By jayherring86 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-02-2016, 10:23 AM
  4. [SOLVED] Formula that list items from a supplier
    By ricdamiani in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-11-2016, 02:31 AM
  5. [SOLVED] Help on finding supplier on list
    By kumarinrajoo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-23-2013, 01:13 AM
  6. [SOLVED] How to list the cheapest supplier for certain products
    By Yelik in forum Excel General
    Replies: 7
    Last Post: 11-05-2012, 02:44 AM
  7. Replies: 2
    Last Post: 07-08-2009, 05:45 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1