Unable to put Countif for date and multiple fields

Abid14

New member
Joined
Sep 5, 2014
Messages
4
Reaction score
0
Points
0
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 18/27/2014 9LowIn progress
Incident 29/2/2014 3HighPending
Incident 38/28/2014 7MediumAssigned
Incident 48/29/2014 7LowResolved
 

Attachments

  • Test1.xlsx
    9.3 KB · Views: 14
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)
 
I think that you want

=COUNTIFS(Sheet1!$D$3:$D$15,"<=7",Sheet1!$F$3:$F$15,"<>Resolved")
 
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".

Please assist.
 
Last edited:
No, it is calculating the not resolved, that is In Progress, Pending and Assigned.

Maybe you should try it!
 
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")
 
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.
 
Last edited:
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")
 
Back
Top