Results 1 to 9 of 9

Nested Countif Formula Question

This is a discussion on Nested Countif Formula Question within the Excel Questions forums, part of the Question Forums category; Hello Forum. I am working on a nested countif formula, however I can't get it together and would like to ...

  1. #1
    Board Regular
    Join Date
    Jan 2010
    Posts
    190

    Default Nested Countif Formula Question

    Hello Forum. I am working on a nested countif formula, however I can't get it together and would like to see if anyone could lend me a hand in figuring it out. Below is a the data between two sheets. The formula that I am trying to write would give me the result on Sheet1, Col D, calculating the Total for Jon at 2. I have tried to nest Countif's, Sumif's and Vlookup's with no success. Thank you for taking the time to read my post and any reply would be greatly appreciated.

    Sheet1
    Col ACol BCol CCol D
    Name:Nbr:Nbr:Total:
    Jon225Jan-112


    Sheet2
    Col ACol BCol C
    Name:Nbr:Service:
    Jon225Jan-11
    Bob110Jan-11
    Sam440Jan-11
    Jon225Jan-11
    Bob110Feb-11
    Sam440Feb-11
    Jon225Dec-10
    Sam440Dec-10

  2. #2
    Board Regular
    Join Date
    Jun 2005
    Location
    London
    Posts
    6,330

    Default Re: Nested Countif Formula Question

    Sheet2

    *ABC
    1Name:Nbr:Service:
    2Jon225Jan-11
    3Bob110Jan-11
    4Sam440Jan-11
    5Jon225Jan-11
    6Bob110Feb-11
    7Sam440Feb-11
    8Jon225Dec-10
    9Sam440Dec-10


    Sheet1

    *ABCD
    1Name:Nbr:Nbr:Total
    2Jon225Jan-112

    Spreadsheet Formulas
    CellFormula
    D2=SUMPRODUCT(--(Name=A2),--(Nbr=B2),--(Service=Sheet1!C2))
    Names in Formulas
    CellNameApplies to
    D2Name=OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,1)
    D2Nbr=OFFSET(Name,0,1)
    D2Service=OFFSET(Name,0,2)


    Excel tables to the web >> Excel Jeanie HTML 4
    Neil

  3. #3
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Nested Countif Formula Question

    Quote Originally Posted by storm925 View Post
    Hello Forum. I am working on a nested countif formula, however I can't get it together and would like to see if anyone could lend me a hand in figuring it out. Below is a the data between two sheets. The formula that I am trying to write would give me the result on Sheet1, Col D, calculating the Total for Jon at 2. I have tried to nest Countif's, Sumif's and Vlookup's with no success. Thank you for taking the time to read my post and any reply would be greatly appreciated.

    Sheet1
    Col ACol BCol CCol D
    Name:Nbr:Nbr:Total:
    Jon225Jan-112


    Sheet2
    Col ACol BCol C
    Name:Nbr:Service:
    Jon225Jan-11
    Bob110Jan-11
    Sam440Jan-11
    Jon225Jan-11
    Bob110Feb-11
    Sam440Feb-11
    Jon225Dec-10
    Sam440Dec-10
    Try this...

    =SUMPRODUCT(--(Sheet2!A2:A9=A2),--(Sheet2!B2:B9=B2),--(YEAR(Sheet2!C2:C9)=YEAR(C2)),--(MONTH(Sheet2!C2:C9)=MONTH(C2)))
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  4. #4
    Board Regular
    Join Date
    Jan 2010
    Posts
    190

    Smile Re: Nested Countif Formula Question

    Thank you njimack, it took me a little while to figure it out... works like a charm. I never thought of using "sumproduct" with "offset". Five Stars!
    --Ben

  5. #5
    Board Regular
    Join Date
    Jan 2010
    Posts
    190

    Default Re: Nested Countif Formula Question

    Hi #NAME?,

    I like the simplicity of your formula, however I get a #NUM! error message.
    Is there something missing?

    --Ben

  6. #6
    Board Regular
    Join Date
    Mar 2005
    Posts
    152

    Default Re: Nested Countif Formula Question

    if you have excel 2007 or higher, you can use COUNTIFS as well.

    Sheet2
    ABCD
    1Sheet1
    2Col ACol BCol CCol D
    3Name:Nbr:Nbr:Total:
    4Jon22511-Jan2
    5
    6
    7Sheet2
    8Col ACol BCol C
    9Name:Nbr:Service:
    10Jon22511-Jan
    11Bob11011-Jan
    12Sam44011-Jan
    13Jon22511-Jan
    14Bob11011-Feb
    15Sam44011-Feb
    16Jon22510-Dec
    17Sam44010-Dec
    Excel 2007

    Worksheet Formulas
    CellFormula
    D4=COUNTIFS($A$10:$A$17,"="&A4,$B$10:$B$17,"="&B4,$C$10:$C$17,"="&C4)


  7. #7
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Nested Countif Formula Question

    Quote Originally Posted by storm925 View Post
    Hi #NAME?,

    I like the simplicity of your formula, however I get a #NUM! error message.
    Is there something missing?

    --Ben
    Are you using entire columns as range references?

    What version of Excel are you using?

    You can't use entire columns as range references in the SUMPRODUCT function in Excel versions prior to Excel 2007.

    On a side note...

    Your dates and date criteria are kind of ambiguous. That's why I tested for both year and month number.
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  8. #8
    Board Regular
    Join Date
    Jan 2010
    Posts
    190

    Smile Re: Nested Countif Formula Question

    #name?

    I used entire columns, so that is the reason why. I'm also using 2003. After correcting this, it worked like a charm. It's also a lot simpler formula to use. Thank you again for your help and keeping it simple. --Ben

  9. #9
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Nested Countif Formula Question

    Quote Originally Posted by storm925 View Post
    #name?

    I used entire columns, so that is the reason why. I'm also using 2003. After correcting this, it worked like a charm. It's also a lot simpler formula to use. Thank you again for your help and keeping it simple. --Ben
    Good deal. Thanks for the feedback!
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com