AVERAGEIFS with 4 criteria

ANDYADAM

New Member
Joined
Mar 4, 2024
Messages
11
Office Version
  1. 2021
Platform
  1. Windows
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

Book1
ABCDEFGHIJKLM
2MONTHYEARTOTAL JOBS FOR MONTHNUMBER COMPLETED JOBSCANCELLED JOBSAVG. CALENDAR LEAD DAYSAVG. CALENDAR DAYS TO COMPLETIONAVG. VISITS PER JOB1 VISIT1 VISIT %2 VISIT2 VISIT %3 VISIT
3JAN202323150153
4FEB2023170
5MAR2023010
6APR2023010
7MAY2023000
8JUN2023000
9JUL2023000
10AUG2023000
11SEP2023000
12OCT2023000
13NOV2023000
14DEC2023000
15
16JOB NUMBER DATE ASSIGNEDPOSTCODEENG IDCUSTOMER NAME1st VISIT DATEMIDDLE DATECOMPLETE DATEFIRST VISIT FIX (Y or N)TOTAL JOB VISITSCALENDAR DAYSREPAIR TIME (DAYS)CURRENT ANS STATUS
1727374704/01/20237056SUTHERLAND23/01/202323/01/20233191INVOICED
1827713304/01/20237028MACGUIRE17/02/202317/02/20231441INVOICED
1927720810/01/20236025DEMPSEY02/02/202302/02/20232231INVOICED
2027801303/01/20237056CLARK 11/01/202311/01/2023181INVOICED
2127810503/01/20237102WILSON10/01/202317/01/20231147JOB COMPLETE - NO INVOICE
2227815104/01/20237008LAWLER12/01/202306/02/202333325INVOICED
2327815204/01/20236025CHEESLEY11/04/202319/04/202321058INVOICED
Sheet1
Cell Formulas
RangeFormula
C3C3=COUNTIFS($B$17:$B$9999,">=01/01/2023",$B$17:$B$9999,"<=31/01/2023")
D3,G3D3=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"}))
E3E3=COUNTIFS($H$17:$H$9999,">=01/01/2023",$H$17:$H$9999,"<=31/01/2023",$M$17:$M$9999,"=JOB CANCELLED")
C4C4=COUNTIFS($B$17:$B$9999,">=01/02/2023",$B$17:$B$9999,"<=28/02/2023")
D4D4=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"}))
E4E4=COUNTIFS($H$17:$H$9999,">=01/02/2023",$H$17:$H$9999,"<=28/02/2023",$M$17:$M$9999,"=JOB CANCELLED")
C5C5=COUNTIFS($B$17:$B$9999,">=01/03/2023",$B$17:$B$9999,"<=31/03/2023")
D5D5=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"}))
E5E5=COUNTIFS($H$17:$H$9999,">=01/03/2023",$H$17:$H$9999,"<=31/03/2023",$M$17:$M$9999,"=JOB CANCELLED")
C6C6=COUNTIFS($B$17:$B$9999,">=01/04/2023",$B$17:$B$9999,"<=30/04/2023")
D6D6=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"}))
E6E6=COUNTIFS($H$17:$H$9999,">=01/04/2023",$H$17:$H$9999,"<=30/04/2023",$M$17:$M$9999,"=JOB CANCELLED")
C7C7=COUNTIFS($B$17:$B$9999,">=01/05/2023",$B$17:$B$9999,"<=31/05/2023")
D7D7=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"}))
E7E7=COUNTIFS($H$17:$H$9999,">=01/05/2023",$H$17:$H$9999,"<=31/05/2023",$M$17:$M$9999,"=JOB CANCELLED")
C8C8=COUNTIFS($B$17:$B$9999,">=01/06/2023",$B$17:$B$9999,"<=30/06/2023")
D8D8=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"}))
E8E8=COUNTIFS($H$17:$H$9999,">=01/06/2023",$H$17:$H$9999,"<=30/06/2023",$M$17:$M$9999,"=JOB CANCELLED")
C9C9=COUNTIFS($B$17:$B$9999,">=01/07/2023",$B$17:$B$9999,"<=31/07/2023")
D9D9=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"}))
E9E9=COUNTIFS($H$17:$H$9999,">=01/07/2023",$H$17:$H$9999,"<=31/07/2023",$M$17:$M$9999,"=JOB CANCELLED")
C10C10=COUNTIFS($B$17:$B$9999,">=01/08/2023",$B$17:$B$9999,"<=31/08/2023")
D10D10=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"}))
E10E10=COUNTIFS($H$17:$H$9999,">=01/08/2023",$H$17:$H$9999,"<=31/08/2023",$M$17:$M$9999,"=JOB CANCELLED")
C11C11=COUNTIFS($B$17:$B$9999,">=01/09/2023",$B$17:$B$9999,"<=30/09/2023")
D11D11=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"}))
E11E11=COUNTIFS($H$17:$H$9999,">=01/09/2023",$H$17:$H$9999,"<=30/09/2023",$M$17:$M$9999,"=JOB CANCELLED")
C12C12=COUNTIFS($B$17:$B$9999,">=01/10/2023",$B$17:$B$9999,"<=31/10/2023")
D12D12=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"}))
E12E12=COUNTIFS($H$17:$H$9999,">=01/10/2023",$H$17:$H$9999,"<=31/10/2023",$M$17:$M$9999,"=JOB CANCELLED")
C13C13=COUNTIFS($B$17:$B$9999,">=01/11/2023",$B$17:$B$9999,"<=30/11/2023")
D13D13=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"}))
E13E13=COUNTIFS($H$17:$H$9999,">=01/11/2023",$H$17:$H$9999,"<=30/11/2023",$M$17:$M$9999,"=JOB CANCELLED")
C14C14=COUNTIFS($B$17:$B$9999,">=01/12/2023",$B$17:$B$9999,"<=31/12/2023")
D14D14=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"}))
E14E14=COUNTIFS($H$17:$H$9999,">=01/12/2023",$H$17:$H$9999,"<=31/12/2023",$M$17:$M$9999,"=JOB CANCELLED")
H3H3=AVERAGEIFS(J17:J9998,B17:B9998,">=01/01/2023",H17:H9998,"<=31/01/2023")
K17:K23K17=H17-B17
L17L17=H17-F17+1
L18:L23L18=IF(H18=F18,1,H18-F18)
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
How about
Excel Formula:
=AVERAGE(FILTER($K$17:$K$9999,($B$17:$B$9999>="01/01/2023")*($H$17:$H$9999<="31/01/2023")*(($M$17:$M$9999="INVOICED")+($M$17:$M$9999="JOB COMPLETE - NO INVOICE"))))
 
Upvote 0
Once again Fluff, many thanks for your time and help. It is much appreciated.

May I ask, in idiot proof language, what does "filter" do?

Thanks
 
Upvote 0
Exactly what it says. It filters the data to return anything that matches all criteria. ;)
 
Upvote 0
How about
Excel Formula:
=AVERAGE(FILTER($K$17:$K$9999,($B$17:$B$9999>="01/01/2023")*($H$17:$H$9999<="31/01/2023")*(($M$17:$M$9999="INVOICED")+($M$17:$M$9999="JOB COMPLETE - NO INVOICE"))))
May be missing criteria for "Job Complete" no?
 
Upvote 0
Exactly what it says. It filters the data to return anything that matches all criteria. ;)
Ok, thanks again
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
A
You're welcome & thanks for the feedback.
Sorry to bother you again Fluff, but the formula is returning #CALC! for me.
Also, if I was to add a third criteria, i presume i just add another +($M$17:$M$23="JOB COMPLETE") to the end of the formula?
Cheers
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top