Forum Discussion

MrManikandanRaju's avatar
MrManikandanRaju
Copper Contributor
Dec 03, 2022
Solved

FIFO formula for payments receivable

How to use the FIFO concept in maintaining the payments received from the customers. Let us assume that there are 4 line items for an invoice and the customer has made a partial payment. Now, I need ...
BI & Data Analysis
excel
Formulas and Functions
Macros and VBA
  • mtarler's avatar
    mtarler
    Dec 03, 2022

    MrManikandanRaju yes I already used SUMIFS so you just need to change the criteria and add another set like this:

    =MIN([@[Net Total]],
   MAX(0,
   SUMIFS(Table1[[#Headers],[Net Total]]:[@[Net Total]],Table1[[#Headers],[Acc Yr]]:[@[Acc Yr]],[@[Acc Yr]],Table1[[#Headers],[Inv No]]:[@[Inv No]],[@[Inv No]])-SUMIFS([Payment Rec''d],[Acc Yr],[@[Acc Yr]],[Inv No],[@[Inv No]])))
MrManikandanRaju's avatar
MrManikandanRaju
Copper Contributor
Dec 03, 2022
Hi Matt. Thank you so much for the help. Appreciate it. In your example you have chosen the criteria as customer name. But I have to have the criteria as both invoice number and accounting year. I think it can be done within SUMIFS function. Please correct me if I am wrong. Let me also try it and let you know. Once again thanks!!
mtarler's avatar
mtarler
Silver Contributor
Dec 03, 2022

MrManikandanRaju yes I already used SUMIFS so you just need to change the criteria and add another set like this:

=MIN([@[Net Total]],
   MAX(0,
   SUMIFS(Table1[[#Headers],[Net Total]]:[@[Net Total]],Table1[[#Headers],[Acc Yr]]:[@[Acc Yr]],[@[Acc Yr]],Table1[[#Headers],[Inv No]]:[@[Inv No]],[@[Inv No]])-SUMIFS([Payment Rec''d],[Acc Yr],[@[Acc Yr]],[Inv No],[@[Inv No]])))
  • MrManikandanRaju's avatar
    MrManikandanRaju
    Copper Contributor
    Dec 04, 2022

    mtarler This works if the data is a table. Also works only when each line item's total is mentioned in Net Total column.

     

    However it is not working when I group the net total by invoice. Why is it so?

    I can convert my data into table. No big deal. But why the formula is not working when I group the net total by invoice?

     

    Attaching sample workbook.

     

    Please help. 

    • mtarler's avatar
      mtarler
      Silver Contributor
      Dec 04, 2022

      MrManikandanRaju  I do recommend you try working with columns when possible but you can convert this formula into non-table references too.  As for an answer to your question about why it doesn't 'work' when you group the Net Totals is because the prior lines of Net Totals are 0 so If the Net Total they owe at/for that point/line is 0 then how could they own more on that line.  The formula could reference the Gross Total but then when would you take into account the tax and of course a formula (not this one) could be made to calculate the Net Total for the line based on the gross * tax but why?

      fyi, here is a break down of line 3

       

       

      =MIN([@[Net Total]],
   MAX(0,
   SUMIFS(Table1[[#Headers],[Net Total]]:[@[Net Total]],Table1[[#Headers],[Acc Yr]]:[@[Acc Yr]],[@[Acc Yr]],Table1[[#Headers],[Inv No]]:[@[Inv No]],[@[Inv No]])-SUMIFS([Payment Rec''d],[Acc Yr],[@[Acc Yr]],[Inv No],[@[Inv No]])))

       

      line 3 is of the form SUMIFS( [sum Net Totals up to this line] ) - SUMIFS( [sum of all payments] )

       

       

Resources