AVERAGEIFS with 4 criteria

A

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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
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
Fluff said:
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"))))
Click to expand...
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
Fluff said:
You're welcome & thanks for the feedback.
Click to expand...
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
You must log in or register to reply here.

Similar threads

S
  • Question Question
List all staff names that worked under a manager on a certain date
Replies
5
Views
403
Fluff
Fluff
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
124
flashgordie
F
S
  • Solved
Count of active employees between dates.
Replies
3
Views
723
Fluff
Fluff
O
  • Solved
Macro to extract repeating invoice numbers and details
Replies
4
Views
245
Omar M
O
L
  • Solved
Help Please
2 3
Replies
21
Views
532
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,224,813
Messages
6,181,114
Members
453,021
Latest member
Justyna P

Share this page

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