Formula to show whether invoice is paid AND/OR approved
Results 1 to 6 of 6

Thread: Formula to show whether invoice is paid AND/OR approved

  1. #1
    Board Regular
    Join Date
    Jul 2019
    Location
    Win 10, Office Professional Plus 2016
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula to show whether invoice is paid AND/OR approved

    ABC
    1Invoice ApprovedPayment Date
    2150001/01/19
    3150001/01/19
    41
    51
    62501
    72501
    82
    92
    103
    113
    12
    13
    14Invoice Status
    151Paid
    162Approved
    173Not Approved

    Sheet3




    Hello guys,

    I have a sheet with invoice information which can be categorized as either:

    - Approved = there is a value in Column B but not in Column C
    - Paid = there is a value in Column C
    - Not Approved= there are no values in Column C AND Column B

    B15:B17 is the output I am looking for.
    How would you go about creating a formula to show this output?

    Invoice 1:
    Rows 4-5 represent the booking of invoice 1
    Rows 2-3 show that invoice 1 has been paid since there is a date in Column C

    FWIW
    If an invoice has been paid, as in the case of invoice 1, I don't need to know whether it has been approved or not.


    Thank you





  2. #2
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Formula to show whether invoice is paid AND/OR approved

    Hi,

    Would there be an instance where, for example, C6 has a date, but C7 does not, what would be the result then?

  3. #3
    Board Regular
    Join Date
    Jul 2019
    Location
    Win 10, Office Professional Plus 2016
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to show whether invoice is paid AND/OR approved

    Quote Originally Posted by jtakw View Post
    Would there be an instance where, for example, C6 has a date, but C7 does not, what would be the result then?
    Hi jtakw - that will not occur, no.
    Last edited by danhendo888; Jul 18th, 2019 at 11:03 PM.

  4. #4
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Formula to show whether invoice is paid AND/OR approved

    Then, try this:

    ABC
    1InvoiceApprovedPayment Date
    215001/1/2019
    315001/1/2019
    41
    51
    62501
    72501
    82
    92
    103
    113
    12
    13
    14InvoiceStatus
    151PaidPaid
    162ApprovedApproved
    173Not ApprovedNot Approved

    Sheet691



    Worksheet Formulas
    CellFormula
    C15=IF(COUNTIFS(A$2:A$11,A15,B$2:B$11,">0"),IF(COUNTIFS(A$2:A$11,A15,C$2:C$11,">0"),"Paid","Approved"),"Not Approved")



    C15 formula copied down, change/adjust cell references/range as needed.

  5. #5
    Board Regular
    Join Date
    Jul 2019
    Location
    Win 10, Office Professional Plus 2016
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to show whether invoice is paid AND/OR approved

    Quote Originally Posted by jtakw View Post
    Then, try this:

    A B C
    1 Invoice Approved Payment Date
    2 1 500 1/1/2019
    3 1 500 1/1/2019
    4 1
    5 1
    6 2 501
    7 2 501
    8 2
    9 2
    10 3
    11 3
    12
    13
    14 Invoice Status
    15 1 Paid Paid
    16 2 Approved Approved
    17 3 Not Approved Not Approved
    Sheet691

    Worksheet Formulas
    Cell Formula
    C15 =IF(COUNTIFS(A$2:A$11,A15,B$2:B$11,">0"),IF(COUNTIFS(A$2:A$11,A15,C$2:C$11,">0"),"Paid","Approved"),"Not Approved")



    C15 formula copied down, change/adjust cell references/range as needed.

    Thank you so much! It works beautifully.
    You've made my day

  6. #6
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Formula to show whether invoice is paid AND/OR approved

    You're welcome, welcome to the forum, and thanks for the feedback.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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