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
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 |