+ Reply to Thread
Results 1 to 18 of 18

Count based on 3 criteria including date range

  1. #1
    Forum Contributor
    Join Date
    08-07-2008
    Location
    Australia
    Posts
    132

    Count based on 3 criteria including date range

    Hi All,

    I have scoured various responses and can't seem to find an answer that suit s my situation.

    Basically, I would like to match 3 criteria based on a range (Note: using excel 2003)

    Column A includes names, B includes Dates, C includes Yes/no

    I have tried a sumproduct but does not returns 0:
    =SUMPRODUCT(($A$2:$A$7=$E3)*($B$2:$B$7>="Jan-13")*($C$2:$C$7=$K$3))

    Anyone know where I may have gone wrong?

    Regards
    Tim
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Count based on 3 criteria including date range

    hi Tim, you cannot put your criteria for dates like that. that will be looking for a text. Dates are actually numbers in Excel. so you can add an additional TEXT formula to convert the range to texts. it will then be apple to apple comparison:
    =SUMPRODUCT(($A$2:$A$7=$E3)*(TEXT($B$2:$B$7,"mmm-yy")>="Jan-13")*($C$2:$C$7=$K$3))

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Contributor
    Join Date
    08-07-2008
    Location
    Australia
    Posts
    132

    Re: Count based on 3 criteria including date range

    Thanks very much for responding benishiryo.

    I learnt a valuable lesson there.

    However, I have applied that formula to attached spreadsheet, cell F5 and it returns value of 1, whereas it should be 2 (that is, BU has Jan-13 and Feb-13, B6 and B7)

    Any thoughts?

    Thanks

  4. #4
    Forum Contributor
    Join Date
    08-07-2008
    Location
    Australia
    Posts
    132

    Re: Count based on 3 criteria including date range

    It does not appear to recognise the >="Jan-13" in the equation (any ideas how to get the > to work?)

    Thanks

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Count based on 3 criteria including date range

    I have 2 questions for you...

    1. what version of excel are you using (please add it to your profile) - this helps in tailoring formulas for the version you have
    scratch the question 1 (2003), but please add your version to your profile
    2. what exactly are you trying to get? can you give a few sample answers?
    Last edited by FDibbins; 06-13-2013 at 12:17 AM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Count based on 3 criteria including date range

    perhaps something like this...
    =SUMPRODUCT(($A$2:$A$7=$E3)*(B2:B7>=DATE(2013,1,1)))

  7. #7
    Forum Contributor
    Join Date
    08-07-2008
    Location
    Australia
    Posts
    132

    Re: Count based on 3 criteria including date range

    Hi FDibbins

    Sorry mentioned in first post, I'm using Excel 2003 (will try add to profile).

    The answers in the attached Results Summary, would be:
    HR 1, 1, 0
    Legal 1, 0, 1
    BU 2, 1, 1

    Given that BU is the only one with multiple matches greater than and equal to Jan-13, it only affects that result.

    Hope that clarifies, look forward to your thoughts.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Count based on 3 criteria including date range

    for >10k...
    =SUMPRODUCT(($A$2:$A$7=$E3)*($B$2:$B$7>=DATE(2013,1,1))*($C$2:$C$7=G$2))
    copy this down AND across

  9. #9
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Count based on 3 criteria including date range

    ahhh i didn't notice you used ">=". that won't work for text. you should use FDibbins' method then. but here's how you can use text with numbers to make sense with greater than or equals to:
    =SUMPRODUCT(($A$2:$A$7=$E3)*(TEXT($B$2:$B$7,"yyymm")>="201301")*($C$2:$C$7=G$2))

    in G3, you should refer to the header like what i did, so that the formulas in Column G & H can be standardized to copy down & across

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Count based on 3 criteria including date range

    Sorry Ben, didnt mean to butt-in, it looked like you were off-line

  11. #11
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Count based on 3 criteria including date range

    @FDibbins:

    yes i was. it's never a problem to me if people contribute different methods or help out. =)
    the goal is to help the OP isn't it? and if people don't butt-in, i'll probably learn lesser.

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Count based on 3 criteria including date range

    Good point I have also learned a bunch by others offering alternative suggestions...thanks

  13. #13
    Forum Contributor
    Join Date
    08-07-2008
    Location
    Australia
    Posts
    132

    Re: Count based on 3 criteria including date range

    Sumproduct test v2.xlsAwesome, thanks FDibbons, that worked great against that attached "cutdown" spreadsheet.

    However, when I tried the same formula on my "real" spreadsheet it doesn't work. Sorry to do this, but do you mind referring to attached new spreadsheet as the > Date does not work?

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Count based on 3 criteria including date range

    your dates are not dates they are text, looking like dates

    Give me a few minutes and I will try and sort it out for you
    (also, you dont actually have any 1/1/2013 dates, not even text that looks like dates)

  15. #15
    Forum Contributor
    Join Date
    08-07-2008
    Location
    Australia
    Posts
    132

    Re: Count based on 3 criteria including date range

    Guys,

    Thanks very much for your assistance, I have tried using Benishiryo's method and it worked (that is counted only those greater and equal to Jan-13).

    For example, this formula in cell G5 of V2 spreadsheet:
    =SUMPRODUCT(($B2:$B$1500=$F5)*(TEXT($C$2:$C$1500,"yyymm")>="201301"))

    Regards
    Tim

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Count based on 3 criteria including date range

    change the formula in C to this...
    =IF(A2="","",DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)))
    This will convert your "text" dates in A to real dates that you can actually work with

  17. #17
    Forum Contributor
    Join Date
    08-07-2008
    Location
    Australia
    Posts
    132

    Re: Count based on 3 criteria including date range

    Thanks, but I tried that and it gave me a figure of 40417?

  18. #18
    Forum Contributor
    Join Date
    08-07-2008
    Location
    Australia
    Posts
    132

    Re: Count based on 3 criteria including date range

    Quote Originally Posted by timarcarze View Post
    Thanks, but I tried that and it gave me a figure of 40417?
    Oh I see when I reformat the cell to reflect a "custom date" it converts the number to the date.

    Thks, I have been schooled!

+ 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