# Thread: Formula to show whether invoice is paid AND/OR approved Thanks:  1 Post #5311842 (1) Likes:  1 Post #5311842 (1)

1. ## 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. ## 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. ## Re: Formula to show whether invoice is paid AND/OR approved

Originally Posted by jtakw
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.

4. ## 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. ## Re: Formula to show whether invoice is paid AND/OR approved

Originally Posted by jtakw
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.

6. ## Re: Formula to show whether invoice is paid AND/OR approved

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