Countifs not returning correct value, need assistance please

Les76

New Member
Joined
Dec 14, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
I am running a countifs formula to pull the "completed on time" work orders for the month, which are those completed within 30 days of issuance. The correct value should be 69 but the formula is returning 46. To verify, I filtered the data down to October completed and ran the difference (Date completed - Date Issued) in an adjacent column and it totals 69 but this formula returns 46. Am I missing something or is there a way to correct the issue:

=COUNTIFS(Dates,">="&DATE($H$1,10,1),Dates,"<="&DATE($H$1,10,31),Type,$A85,Status,$C$2,Completion,"<="&Dates+30)

Where: Dates - Issue Date; H1 - Current Year; Type and Status - Type of work and status of work order; Completion - Completion date

I've verified that everything up to criteria 5 functions properly and I'm using Excel 2016.

1607982415793.png


1607982278038.png


Thank you for any assistance you can provide as this is driving me crazy!!!
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

You have not structured your COUNTIFS function correctly.
It is:
=COUNTIFS(range1, condition1, range2, condition2, etc...)

See: How to use the Excel COUNTIFS function | Exceljet

If you have multiple conditions to apply on the same range, it still needs to be structured like shown above. Just repeat the range reference.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Try wrapping your formula in SUM.
It may also need array entry, ie Ctrl Shift Enter rather than just Enter
 

Les76

New Member
Joined
Dec 14, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
Welcome to the Board!

You have not structured your COUNTIFS function correctly.
It is:
=COUNTIFS(range1, condition1, range2, condition2, etc...)

See: How to use the Excel COUNTIFS function | Exceljet

If you have multiple conditions to apply on the same range, it still needs to be structured like shown above. Just repeat the range reference.
Joe, thank you for the welcome. My formula lists 5 conditions and 5 criteria, so I'm not sure what you mean? Dates, Type, Status and Completion are named ranges. When I eliminate the Completion,"<="&Dates+30, the formula works as it should but doesn't give me the count I'm interested in. There is something wrong with the last piece

=COUNTIFS(Dates,">="&DATE($H$1,10,1),Dates,"<="&DATE($H$1,10,31),Type,$A85,Status,$C$2,Completion,"<="&Dates+30)
 

Les76

New Member
Joined
Dec 14, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Try wrapping your formula in SUM.
It may also need array entry, ie Ctrl Shift Enter rather than just Enter
SUM returned the same value and array entry resulted in 0. Thanks for the suggestions Fluff!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Are your named ranges Dates, Type & Completion all the same size?
 

Les76

New Member
Joined
Dec 14, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I created another named range (Month) wherein I added 30 days to the issue date and tried modifying the formula to reference the new named range and eliminating the Status condition. This new formula results in the same wrong answer:
=COUNTIFS(Dates,">="&DATE($H$1,10,1),Dates,"<="&DATE($H$1,10,31),Type,$A85,Completion,"<="&Month)
 

Les76

New Member
Joined
Dec 14, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
Are your named ranges Dates, Type & Completion all the same size?
I named the full column but some currently have blanks as not all are completed yet.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
Joe, thank you for the welcome. My formula lists 5 conditions and 5 criteria, so I'm not sure what you mean? Dates, Type, Status and Completion are named ranges.
Apologies. I misunderstood that fact.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Can you post some sample date data using the XL2BB add-in that shows the problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

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
Top