+ Reply to Thread
Results 1 to 15 of 15

Countifs with multiple criteria

  1. #1
    Registered User
    Join Date
    01-22-2013
    Location
    Heidelberg, Germany
    MS-Off Ver
    Excel 2010
    Posts
    15

    Countifs with multiple criteria

    Excel magicians,

    I am using this formula in cell S9: =COUNTIFS(H:H,">="&DATE(2012,10,1),H:H,"<="&DATE(2012,12,31))

    to count the number within these dates (due dates) and is working nicely. My problem is adding on to it. I need a formula in cell T9 that would count if there is a date in column I ONLY if it falls within the dates in the previous formula. Lastly a formula in cell T9 that would give me a count of the completed (column I) that is mor than 95 day beyond the dates in the original formula. Hope I explained it well enough. Thanks in advance.
    Last edited by yenaled; 01-24-2013 at 05:34 AM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,602

    Re: Countifs

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.


  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,602

    Re: Countifs with multiple criteria

    Do you mean this in T9:
    Please Login or Register  to view this content.
    And I don't understand 2nd part since 95 days is more than 3 months so there would be nothing between two criteria?

  4. #4
    Registered User
    Join Date
    01-22-2013
    Location
    Heidelberg, Germany
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Countifs with multiple criteria

    thank you but that does not work because all that does is give me a count of all dates that fall wihinin that range.

    This is what I am trying to do.

    Formulate a report of reviews that are due (column H) last quarter (done with my original formula), count how many reviews that due last quarter were actually completed (column H)[jsut counting a date range in H does not tell me if the completed were due in the 1st QTR], and lastlya count if the completed review (column I) is more than 5 days past the due dat (column H).

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,602

    Re: Countifs with multiple criteria

    OK, there was example 16 in that cell so I get that result.
    Now I get result 12.
    is that right?

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,602

    Re: Countifs with multiple criteria

    And for second formula you need for 5 days longer period of I if H is same?

    I got result 14.
    Is that right?

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-22-2013
    Location
    Heidelberg, Germany
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Countifs with multiple criteria

    I'm gettin an error with that formula but it still would not work I believe. I need a formula that states if the date in column H is xxxx then count in column I f the same row.

  8. #8
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,602

    Re: Countifs with multiple criteria

    Works for me:

  9. #9
    Registered User
    Join Date
    01-22-2013
    Location
    Heidelberg, Germany
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Countifs with multiple criteria

    Good thing is that I got the formula to work but it still is not accurate. I am attaching the spreadsheet you jsut sent except filterd by 1st review due dates of only OCT, NOV, DEC of 2012 which is what is on the formula. It show 16 like the original formula shows, but if you look in the "completed" column, only one is blank so I needa formula that would calculate 15.
    Attached Files Attached Files

  10. #10
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,602

    Re: Countifs with multiple criteria

    Here, try this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    but in your example that cell wasnt actually balnk so you might get 16. Copy paste blank cell into I16
    Last edited by zbor; 01-24-2013 at 07:15 AM.

  11. #11
    Registered User
    Join Date
    01-22-2013
    Location
    Heidelberg, Germany
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Countifs with multiple criteria

    I really appreciate your help, but i am getting another error with this.

  12. #12
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,602

    Re: Countifs with multiple criteria

    Try replace ; with ,

  13. #13
    Registered User
    Join Date
    01-22-2013
    Location
    Heidelberg, Germany
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Countifs with multiple criteria

    That was the first thing I did and also took out on of the " at the end think it could be the problem. I understand where you were goin going with this and is what I first thought would work, but hasn't

  14. #14
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,602

    Re: Countifs with multiple criteria

    I really don't know what doesn't work for you since they work here:
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    01-22-2013
    Location
    Heidelberg, Germany
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Countifs with multiple criteria

    The first formula I have working! Ok, so onto the second formula. I need to count (using the same criteria) if there are any completed that are greater than or equat to due date plus 5.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1