COUNTIFS FORMULA RETURNING ZERO

[ANDYADAM]

Hi all,

I'm trying to create a COUNTIFS formula with four criteria.

The first is to determine if the date is greater than or equal to 01/01/2023. The second is to determine if the date is less than or equal to 31/01/2023.

If the first two criteria are true, then the third and fourth criteria are to count if the cell entry is 'Invoiced' AND 'Job Complete - No Invoice'.

I can't seem to see the wood for the trees.

Any advice will be much appreciated.

Thanks
Andy

Book1
	A	B	C
1	DATE ASSIGNED	NUMBER COMPLETED	CURRENT STATUS
2	04/01/2023	0	INVOICED
3	10/02/2023		INVOICED
4	03/01/2023		INVOICED
5	03/04/2023		INVOICED
6	04/01/2023		INVOICED
7	04/01/2023		NO RESPONSE
8	25/02/2023		NO RESPONSE
9	03/01/2023		APP
10	07/02/2023		APP
11	06/03/2023		ON HOLD
12	06/03/2023		ON HOLD
13	06/03/2023		JOB COMPLETE - NO INVOICE
14	06/09/2023		JOB COMPLETE - NO INVOICE
15	06/03/2023		APP
Sheet1

Cell Formulas
Range	Formula
B2	B2	=COUNTIFS(A2:A15,">=01/01/2023",A2:A15,"<=31/01/2023",C2:C15,"=INVOICED",C2:C15,"=JOB COMPLETE - NO INVOICE")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C7:C8	Expression	=ISBLANK($M7)	text	YES
C7:C8	Expression	=$M7="TO BE BOOKED"	text	NO
C7:C8	Expression	=$M7="PARTS ORDERED"	text	NO
C7:C8	Expression	=$M7="PARTS DISPATCHED"	text	NO
C7:C8	Expression	=$M7="ON-HOLD"	text	NO
C7:C8	Expression	=$M7="NO RESPONSE"	text	NO
C7:C8	Expression	=$M7="JOB COMPLETE - NO INVOICE"	text	NO
C7:C8	Expression	=$M7="JOB COMPLETE"	text	NO
C7:C8	Expression	=$M7="JOB CANCELLED"	text	NO
C7:C8	Expression	=$M7="INVOICED"	text	NO
C7:C8	Expression	=$M7="APPOINTMENT DATE"	text	NO
C7:C8	Expression	=$M7="TECH ADVICE"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C7:C8	List	=$M$1:$M$15

 

[Fluff]

Hi & welcome to MrExcel.
You need to use an OR for col C not an AND

=sum(COUNTIFS(A2:A15,">=01/01/2023",A2:A15,"<=31/01/2023",C2:C15,{"INVOICED","JOB COMPLETE - NO INVOICE"}))

 

[ANDYADAM]

Hi & welcome to MrExcel.
You need to use an OR for col C not an AND

=sum(COUNTIFS(A2:A15,">=01/01/2023",A2:A15,"<=31/01/2023",C2:C15,{"INVOICED","JOB COMPLETE - NO INVOICE"}))

Thank you soooo much Fluff.
I've been looking at this for hours. It's fair to say my excel knowledge is pretty grim, but I'm learning slowly.
Cheers

 

[Fluff]

You're welcome & thanks for the feedback.