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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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

A
  • Solved
COUNTIFS FORMULA RETURNING ZERO
Replies
3
Views
125
Fluff
Fluff
M
  • Solved
Formula Help.
Replies
3
Views
63
Fluff
Fluff
S
  • Solved
Find Text in a cell
Replies
5
Views
141
Fluff
Fluff
RichTom44
  • Question
How to highlight a certain time range
Replies
3
Views
304
SanjayGMusafir
SanjayGMusafir
J
  • Solved
Add 1 day to a date within a range as long as 2 conditions are met [without macros]
Replies
4
Views
566
juanam
J

Forum statistics

Threads
1,215,069
Messages
6,122,952
Members
449,095
Latest member
nmaske

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