Hi all,
I'm slowly picking up learning the use of formulas, but i have to say, this has me beat to the point of migraine.
In cell G3 I am trying to calculate the average days based on the figures in K17:K23, when the following criteria is met:
a. B17:B23 is ">=01/01/2023"
b. H17:H23 is "<=31/01/2023"
c. M17:M23 is equal to either "Invoiced", "Job Complete" or "Job Complete - No Invoice".
Any help would be much appreciated.
Thanks
I'm slowly picking up learning the use of formulas, but i have to say, this has me beat to the point of migraine.
In cell G3 I am trying to calculate the average days based on the figures in K17:K23, when the following criteria is met:
a. B17:B23 is ">=01/01/2023"
b. H17:H23 is "<=31/01/2023"
c. M17:M23 is equal to either "Invoiced", "Job Complete" or "Job Complete - No Invoice".
Any help would be much appreciated.
Thanks
Book1 | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
2 | MONTH | YEAR | TOTAL JOBS FOR MONTH | NUMBER COMPLETED JOBS | CANCELLED JOBS | AVG. CALENDAR LEAD DAYS | AVG. CALENDAR DAYS TO COMPLETION | AVG. VISITS PER JOB | 1 VISIT | 1 VISIT % | 2 VISIT | 2 VISIT % | 3 VISIT | ||
3 | JAN | 2023 | 23 | 15 | 0 | 15 | 3 | ||||||||
4 | FEB | 2023 | 1 | 7 | 0 | ||||||||||
5 | MAR | 2023 | 0 | 1 | 0 | ||||||||||
6 | APR | 2023 | 0 | 1 | 0 | ||||||||||
7 | MAY | 2023 | 0 | 0 | 0 | ||||||||||
8 | JUN | 2023 | 0 | 0 | 0 | ||||||||||
9 | JUL | 2023 | 0 | 0 | 0 | ||||||||||
10 | AUG | 2023 | 0 | 0 | 0 | ||||||||||
11 | SEP | 2023 | 0 | 0 | 0 | ||||||||||
12 | OCT | 2023 | 0 | 0 | 0 | ||||||||||
13 | NOV | 2023 | 0 | 0 | 0 | ||||||||||
14 | DEC | 2023 | 0 | 0 | 0 | ||||||||||
15 | |||||||||||||||
16 | JOB NUMBER | DATE ASSIGNED | POSTCODE | ENG ID | CUSTOMER NAME | 1st VISIT DATE | MIDDLE DATE | COMPLETE DATE | FIRST VISIT FIX (Y or N) | TOTAL JOB VISITS | CALENDAR DAYS | REPAIR TIME (DAYS) | CURRENT ANS STATUS | ||
17 | 273747 | 04/01/2023 | 7056 | SUTHERLAND | 23/01/2023 | 23/01/2023 | 3 | 19 | 1 | INVOICED | |||||
18 | 277133 | 04/01/2023 | 7028 | MACGUIRE | 17/02/2023 | 17/02/2023 | 1 | 44 | 1 | INVOICED | |||||
19 | 277208 | 10/01/2023 | 6025 | DEMPSEY | 02/02/2023 | 02/02/2023 | 2 | 23 | 1 | INVOICED | |||||
20 | 278013 | 03/01/2023 | 7056 | CLARK | 11/01/2023 | 11/01/2023 | 1 | 8 | 1 | INVOICED | |||||
21 | 278105 | 03/01/2023 | 7102 | WILSON | 10/01/2023 | 17/01/2023 | 1 | 14 | 7 | JOB COMPLETE - NO INVOICE | |||||
22 | 278151 | 04/01/2023 | 7008 | LAWLER | 12/01/2023 | 06/02/2023 | 3 | 33 | 25 | INVOICED | |||||
23 | 278152 | 04/01/2023 | 6025 | CHEESLEY | 11/04/2023 | 19/04/2023 | 2 | 105 | 8 | INVOICED | |||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C3 | C3 | =COUNTIFS($B$17:$B$9999,">=01/01/2023",$B$17:$B$9999,"<=31/01/2023") |
D3,G3 | D3 | =SUM(COUNTIFS($H$17:$H$9999,">=01/01/2023",$H$17:$H$9999,"<=31/01/2023",$M$17:$M$9999,{"INVOICED","JOB COMPLETE - NO INVOICE"})) |
E3 | E3 | =COUNTIFS($H$17:$H$9999,">=01/01/2023",$H$17:$H$9999,"<=31/01/2023",$M$17:$M$9999,"=JOB CANCELLED") |
C4 | C4 | =COUNTIFS($B$17:$B$9999,">=01/02/2023",$B$17:$B$9999,"<=28/02/2023") |
D4 | D4 | =SUM(COUNTIFS($H$17:$H$9999,">=01/02/2023",$H$17:$H$9999,"<=28/02/2023",$M$17:$M$9999,{"INVOICED","JOB COMPLETE - NO INVOICE"})) |
E4 | E4 | =COUNTIFS($H$17:$H$9999,">=01/02/2023",$H$17:$H$9999,"<=28/02/2023",$M$17:$M$9999,"=JOB CANCELLED") |
C5 | C5 | =COUNTIFS($B$17:$B$9999,">=01/03/2023",$B$17:$B$9999,"<=31/03/2023") |
D5 | D5 | =SUM(COUNTIFS($H$17:$H$9999,">=01/03/2023",$H$17:$H$9999,"<=31/03/2023",$M$17:$M$9999,{"INVOICED","JOB COMPLETE - NO INVOICE"})) |
E5 | E5 | =COUNTIFS($H$17:$H$9999,">=01/03/2023",$H$17:$H$9999,"<=31/03/2023",$M$17:$M$9999,"=JOB CANCELLED") |
C6 | C6 | =COUNTIFS($B$17:$B$9999,">=01/04/2023",$B$17:$B$9999,"<=30/04/2023") |
D6 | D6 | =SUM(COUNTIFS($H$17:$H$9999,">=01/04/2023",$H$17:$H$9999,"<=30/04/2023",$M$17:$M$9999,{"INVOICED","JOB COMPLETE - NO INVOICE"})) |
E6 | E6 | =COUNTIFS($H$17:$H$9999,">=01/04/2023",$H$17:$H$9999,"<=30/04/2023",$M$17:$M$9999,"=JOB CANCELLED") |
C7 | C7 | =COUNTIFS($B$17:$B$9999,">=01/05/2023",$B$17:$B$9999,"<=31/05/2023") |
D7 | D7 | =SUM(COUNTIFS($H$17:$H$9999,">=01/05/2023",$H$17:$H$9999,"<=31/05/2023",$M$17:$M$9999,{"INVOICED","JOB COMPLETE - NO INVOICE"})) |
E7 | E7 | =COUNTIFS($H$17:$H$9999,">=01/05/2023",$H$17:$H$9999,"<=31/05/2023",$M$17:$M$9999,"=JOB CANCELLED") |
C8 | C8 | =COUNTIFS($B$17:$B$9999,">=01/06/2023",$B$17:$B$9999,"<=30/06/2023") |
D8 | D8 | =SUM(COUNTIFS($H$17:$H$9999,">=01/06/2023",$H$17:$H$9999,"<=30/06/2023",$M$17:$M$9999,{"INVOICED","JOB COMPLETE - NO INVOICE"})) |
E8 | E8 | =COUNTIFS($H$17:$H$9999,">=01/06/2023",$H$17:$H$9999,"<=30/06/2023",$M$17:$M$9999,"=JOB CANCELLED") |
C9 | C9 | =COUNTIFS($B$17:$B$9999,">=01/07/2023",$B$17:$B$9999,"<=31/07/2023") |
D9 | D9 | =SUM(COUNTIFS($H$17:$H$9999,">=01/07/2023",$H$17:$H$9999,"<=31/07/2023",$M$17:$M$9999,{"INVOICED","JOB COMPLETE - NO INVOICE"})) |
E9 | E9 | =COUNTIFS($H$17:$H$9999,">=01/07/2023",$H$17:$H$9999,"<=31/07/2023",$M$17:$M$9999,"=JOB CANCELLED") |
C10 | C10 | =COUNTIFS($B$17:$B$9999,">=01/08/2023",$B$17:$B$9999,"<=31/08/2023") |
D10 | D10 | =SUM(COUNTIFS($H$17:$H$9999,">=01/08/2023",$H$17:$H$9999,"<=31/08/2023",$M$17:$M$9999,{"INVOICED","JOB COMPLETE - NO INVOICE"})) |
E10 | E10 | =COUNTIFS($H$17:$H$9999,">=01/08/2023",$H$17:$H$9999,"<=31/08/2023",$M$17:$M$9999,"=JOB CANCELLED") |
C11 | C11 | =COUNTIFS($B$17:$B$9999,">=01/09/2023",$B$17:$B$9999,"<=30/09/2023") |
D11 | D11 | =SUM(COUNTIFS($H$17:$H$9999,">=01/09/2023",$H$17:$H$9999,"<=30/09/2023",$M$17:$M$9999,{"INVOICED","JOB COMPLETE - NO INVOICE"})) |
E11 | E11 | =COUNTIFS($H$17:$H$9999,">=01/09/2023",$H$17:$H$9999,"<=30/09/2023",$M$17:$M$9999,"=JOB CANCELLED") |
C12 | C12 | =COUNTIFS($B$17:$B$9999,">=01/10/2023",$B$17:$B$9999,"<=31/10/2023") |
D12 | D12 | =SUM(COUNTIFS($H$17:$H$9999,">=01/10/2023",$H$17:$H$9999,"<=31/10/2023",$M$17:$M$9999,{"INVOICED","JOB COMPLETE - NO INVOICE"})) |
E12 | E12 | =COUNTIFS($H$17:$H$9999,">=01/10/2023",$H$17:$H$9999,"<=31/10/2023",$M$17:$M$9999,"=JOB CANCELLED") |
C13 | C13 | =COUNTIFS($B$17:$B$9999,">=01/11/2023",$B$17:$B$9999,"<=30/11/2023") |
D13 | D13 | =SUM(COUNTIFS($H$17:$H$9999,">=01/11/2023",$H$17:$H$9999,"<=30/11/2023",$M$17:$M$9999,{"INVOICED","JOB COMPLETE - NO INVOICE"})) |
E13 | E13 | =COUNTIFS($H$17:$H$9999,">=01/11/2023",$H$17:$H$9999,"<=30/11/2023",$M$17:$M$9999,"=JOB CANCELLED") |
C14 | C14 | =COUNTIFS($B$17:$B$9999,">=01/12/2023",$B$17:$B$9999,"<=31/12/2023") |
D14 | D14 | =SUM(COUNTIFS($H$17:$H$9999,">=01/12/2023",$H$17:$H$9999,"<=31/12/2023",$M$17:$M$9999,{"INVOICED","JOB COMPLETE - NO INVOICE"})) |
E14 | E14 | =COUNTIFS($H$17:$H$9999,">=01/12/2023",$H$17:$H$9999,"<=31/12/2023",$M$17:$M$9999,"=JOB CANCELLED") |
H3 | H3 | =AVERAGEIFS(J17:J9998,B17:B9998,">=01/01/2023",H17:H9998,"<=31/01/2023") |
K17:K23 | K17 | =H17-B17 |
L17 | L17 | =H17-F17+1 |
L18:L23 | L18 | =IF(H18=F18,1,H18-F18) |