Formula to show whether invoice is paid AND/OR approved

[danhendo888]

Book1
	A	B	C
1	Invoice	Approved	Payment Date
2	1	500	01/01/19
3	1	500	01/01/19
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
16	2	Approved
17	3	Not 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

 

[jtakw]

Hi,

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

 

[danhendo888]

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.

 

[jtakw]

Then, try this:

Book1
	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

Cell Formulas
Range	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.

 

[danhendo888]

Then, try this:

	A	B	C
Invoice	Approved	Payment Date
Invoice	Status
Paid	Paid
Approved	Approved
Not Approved	Not Approved

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1/1/2019[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1/1/2019[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]501[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]501[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]3[/TD]

</tbody>

Sheet691​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C15[/TH]
[TD="align: left"]=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")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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

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

 

[jtakw]

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