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)
 
Sort by date Sort by votes
Fluff said:
That suggests that nothing matches the criteria. Is this correct

or should that be col H?


Yes but it needs to go before the last 3 closing brackets.
Click to expand...
Just checked all the cells and there are cells which match the criteria.

So in recap, the data to be averaged is in K17:K23 when the following is true:
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".

Thanks
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
That's what the formula is doing & you haven't answered my question.
 
Upvote 0
Fluff said:
That suggests that nothing matches the criteria. Is this correct

or should that be col H?


Yes but it needs to go before the last 3 closing brackets.
Click to expand...
Hi Fluff,
I've checked the data and there are cells which meet the criteria.
The average required is from column K, when
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".

Thanks
 
Upvote 0
In that case try
Excel Formula: 
=AVERAGE(FILTER($K$17:$K$9999,($B$17:$B$9999>=DATE(2023,1,1))*($H$17:$H$9999<=DATE(2023,1,31))*(($M$17:$M$9999="INVOICED")+($M$17:$M$9999="JOB COMPLETE - NO INVOICE"))))
 
Upvote 0
Solution
Fab. that's perfect
Fluff said:
=AVERAGE(FILTER($K$17:$K$9999,($B$17:$B$9999>=DATE(2023,1,1))*($H$17:$H$9999<=DATE(2023,1,31))*(($M$17:$M$9999="INVOICED")+($M$17:$M$9999="JOB COMPLETE - NO INVOICE")))
Click to expand...
 
Upvote 0
You're welcome & thanks for the feedback.
 
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
301
Fluff
Fluff
S
  • Solved
Count of active employees between dates.
Replies
3
Views
616
Fluff
Fluff
O
  • Solved
Macro to extract repeating invoice numbers and details
Replies
4
Views
221
Omar M
O
L
  • Solved
Help Please
2 3
Replies
21
Views
404
Asbestos_Jen
Asbestos_Jen
O
  • Solved
Populate headers based on column and merge amount for multiple words
Replies
7
Views
194
Omar M
O

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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