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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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
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