# Thread: Unable to put Countif for date and multiple fields

1. ## Unable to put Countif for date and multiple fields

Hi Gurus.

I have a sheet on which the data is like below. Date - Age (Formula ABS(TODAY()-A1) - Priority - Status. I want the report in other sheet that, the total count if any incident is less than or equals to 7 days and is in Status "In Progress" or "Assigned" or "Pending". It should not calculate "Resolved" and those which are old than 7 days.

 Incident 1 8/27/2014 9 Low In progress Incident 2 9/2/2014 3 High Pending Incident 3 8/28/2014 7 Medium Assigned Incident 4 8/29/2014 7 Low Resolved

2. Do you mean possibly?

In A2 of Sheet2:

=IFERROR(INDEX(Sheet1!B\$3:B\$15,SMALL(IF((Sheet1!\$D\$3:\$D\$15<=7)*(Sheet1!\$F\$3:\$F\$15<>"Resolved"),ROW(Sheet1!\$B\$3:\$B\$15)-ROW(Sheet1!\$B\$3)+1),ROWS(\$A\$2:\$A2))),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER, then copied down (and if desired, also across to get other info)

3. I think that you want

=COUNTIFS(Sheet1!\$D\$3:\$D\$15,"<=7",Sheet1!\$F\$3:\$F\$15,"<>Resolved")

4. Sorry, but I am not getting the result by either of the above solutions. I want the total of statuses "In Progress", "Pending", And "Assigned", which are less than or equals to 7 days old (as per the Age Column) in one cell. By above formulas it is calculating only "Resolved" or if I change it to any other status. That too it is not considering the age criteria.

As per the attached sheet, I there are total 4 incidents less than or equals to 7 days of age. As per my requirement the formula should give me result 3, for only 2 "Pending" and 1 "In Progress" status. It should not calculate "Resolved".

5. No, it is calculating the not resolved, that is In Progress, Pending and Assigned.

Maybe you should try it!

6. I suspect a language problem, Abid do you want three answers?

In Progress: =COUNTIFS(Sheet1!\$D\$3:\$D\$15,"<=7",Sheet1!\$F\$3:\$F\$15,"=In Progress")
Pending: =COUNTIFS(Sheet1!\$D\$3:\$D\$15,"<=7",Sheet1!\$F\$3:\$F\$15,"=Pending")
Assigned: =COUNTIFS(Sheet1!\$D\$3:\$D\$15,"<=7",Sheet1!\$F\$3:\$F\$15,"=Assigned")

7. Thanks Bob, it works. Thanks for your help. Bob, I have another situation. I want to add one more criteria "Reassigned" alongwith Resolved. I mean, the formula should exclude Resolved as well Reassigned. How can I add this to above formula provided by you.

8. Count the reassigneds and subtract that number

=COUNTIFS(Sheet1!\$D\$3:\$D\$15,"<=7",Sheet1!\$F\$3:\$F\$15,"<>Resolved")-COUNTIFS(Sheet1!\$D\$3:\$D\$15,"<=7",Sheet1!\$F\$3:\$F\$15,"Reassigned")

9. Thanks a tonnnnn Bob.

10. Originally Posted by Bob Phillips
I think that you want

=COUNTIFS(Sheet1!\$D\$3:\$D\$15,"<=7",Sheet1!\$F\$3:\$F\$15,"<>Resolved")
LOL... looks like I misinterprteted

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•