Countifs not returning correct value, need assistance please

[Les76]

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.

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

 

[Joe4]

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]

Try wrapping your formula in SUM.
It may also need array entry, ie Ctrl Shift Enter rather than just Enter

 

[Les76]

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]

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]

Are your named ranges Dates, Type & Completion all the same size?

 

[Les76]

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]

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]

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]

Can you post some sample date data using the XL2BB add-in that shows the problem.