Results 1 to 10 of 10

Thread: Unable to put Countif for date and multiple fields

  1. #1

    Unable to put Countif for date and multiple fields



    Register for a FREE account, and/
    or Log in to avoid these ads!

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

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    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. #3
    I think that you want

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

  4. #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".

    Please assist.
    Last edited by Abid14; 2014-09-07 at 08:42 AM.

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

    Maybe you should try it!

  6. #6
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    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. #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.
    Last edited by Abid14; 2014-09-08 at 12:48 PM.

  8. #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. #9
    Thanks a tonnnnn Bob.

  10. #10
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Quote Originally Posted by Bob Phillips View Post
    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
  •