# Countifs not returning correct value, need assistance please

#### Les76

##### New Member
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!!!

### 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

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
Try wrapping your formula in SUM.
It may also need array entry, ie Ctrl Shift Enter rather than just Enter

#### Les76

##### New Member
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

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
Are your named ranges Dates, Type & Completion all the same size?

#### Les76

##### New Member

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
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

##### MrExcel MVP, Moderator
Can you post some sample date data using the XL2BB add-in that shows the problem.

Replies
4
Views
549
Replies
5
Views
270
Replies
1
Views
316
Replies
0
Views
73
Replies
9
Views
300

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.

### Which adblocker are you using?

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

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