Counting Past Due Reports however with certain Exclusions

[jianh]

Hi,

My first post here however lurker for several weeks, while pulling my hair on a stuck Excel spreadsheet.

How do I count how many submissions are past due date to today's date, while excluding actual submission dates (submitted means no longer overdue), for Purchase Order M-54210 only?

Thanks in advance.

 

[Toadstool]

Hi jianh,

Does this do what you want?

jianh.xlsx
	A	B	C	D	E
1	Planned	Actual	PO	Count of	M-54210
2	10-Aug-21		M-54210	Result	3
3	03-Mar-21	04-Apr-21	M-54210
4	12-Dec-21		M-54210
5	09-Sep-21		M-54210
6	02-Feb-21		M-99999
7	01-Jan-20		M-54210
8	11-Nov-21		M-99999
9	05-May-21		M-54210
10	11-Nov-21	01-Jan-21	M-54210
11	02-Feb-21		M-54210
Sheet1

Cell Formulas
Range	Formula
E2	E2	=COUNTIFS($A$2:$A$9999,"<"&TODAY(),$B$2:$B$9999,"",$C$2:$C$9999,$E$1)

 

[jianh]

Hi jianh,

Does this do what you want?

jianh.xlsx
	A	B	C	D	E
1	Planned	Actual	PO	Count of	M-54210
2	10-Aug-21		M-54210	Result	3
3	03-Mar-21	04-Apr-21	M-54210
4	12-Dec-21		M-54210
5	09-Sep-21		M-54210
6	02-Feb-21		M-99999
7	01-Jan-20		M-54210
8	11-Nov-21		M-99999
9	05-May-21		M-54210
10	11-Nov-21	01-Jan-21	M-54210
11	02-Feb-21		M-54210
Sheet1

Cell Formulas
Range	Formula
E2	E2	=COUNTIFS($A$2:$A$9999,"<"&TODAY(),$B$2:$B$9999,"",$C$2:$C$9999,$E$1)

Awesome Toadstool, let me give this a go in a bit and I'll let you know. Cheers.

 

[jianh]

Yes ToadStool! Thank you! SOLVED!

 

[Toadstool]

Yes ToadStool! Thank you! SOLVED!

You're welcome!
Thanks for the feedback.

 

[James Clear]

Hi jianh,

Does this do what you want?

jianh.xlsx
	A	B	C	D	E
1	Planned	Actual	PO	Count of	M-54210
2	10-Aug-21		M-54210	Result	3
3	03-Mar-21	04-Apr-21	M-54210
4	12-Dec-21		M-54210
5	09-Sep-21		M-54210
6	02-Feb-21		M-99999
7	01-Jan-20		M-54210
8	11-Nov-21		M-99999
9	05-May-21		M-54210
10	11-Nov-21	01-Jan-21	M-54210
11	02-Feb-21		M-54210
Sheet1

Cell Formulas
Range	Formula
E2	E2	=COUNTIFS($A$2:$A$9999,"<"&TODAY(),$B$2:$B$9999,"",$C$2:$C$9999,$E$1)

I am getting an answer 3 , however with the ">" greater than sign and not "<" less than sign

=COUNTIFS($A$2:$A$9999,">"&TODAY(),$B$2:$B$9999,"",$C$2:$C$9999,$E$1)

 

[Toadstool]

I am getting an answer 3 , however with the ">" greater than sign and not "<" less than sign

=COUNTIFS($A$2:$A$9999,">"&TODAY(),$B$2:$B$9999,"",$C$2:$C$9999,$E$1)

That's because there are also 3 dates for PO M-54210 without Actual date entries which are greater than today.

Change that row 2 entry 10-Aug-21 to 7-Jul-2021 and we'll have another date before today (31-Jul-21) which meet the criteria so it returns 4 for me. Your formula would find one less greater than today which also meet the other criteria so would become 2.