+ Reply to Thread
Results 1 to 18 of 18

Need to count repeated names only once that meet multiple criteria

  1. #1
    Registered User
    Join Date
    12-09-2015
    Location
    england
    MS-Off Ver
    2003
    Posts
    33

    Need to count repeated names only once that meet multiple criteria

    Hi,

    I'm trying to count the no. of users that have submitted work returns (what work they've done that day) where multiple criteria is met. I am using Excel 2003. I cannot attach any data due to stupid restrictions with our software. So, let's say the data displays as follows.

    Column a = Persons name
    Column B = Manager (not relevant but shows in my data)
    Column C = Team name
    Column D = Grade (D or E)
    Column E = date


    For example, can someone help me with a formula in Excel 2003 that will count the no. of users that (as an example) work in the 'Health and Welfare team' at Grade D that have submitted data between the dates of 01/04/15 and 03/04/15. If I could upload some data I would but I can't. In an ideal world the data would show multiple (let's say 3) people that have submitted data that meet all of that criteria but where each one has submitted data 2 or more times. This way I will be able to understand the solution better when displayed. So if 3 people have submitted data that meet this criteria and there are 7 entries for example I would want the result to be 3 as this is the no. of unique users.

    I would really appreciate any help someone can provide!

    I have tried using DCOUNTA with an If statement (to start, thinking I could then go onto multiple if statements) but not having much luck.
    Last edited by steeler11111; 12-10-2015 at 11:02 AM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Need to count repeated names only once that meet multiple criteria

    Please post a small sample file (with expected results) which will enable respondents to test any solutions offered. Respondents do want to, nor have time, to generate data
    Last edited by JohnTopley; 12-10-2015 at 08:39 AM.

  3. #3
    Registered User
    Join Date
    12-09-2015
    Location
    england
    MS-Off Ver
    2003
    Posts
    33

    Re: Need to count repeated names only once that meet multiple criteria

    As stated in my original post, restrictions on my computer prevent me from uploading a sample (I work for the government) otherwise I would. I even put together a sample ready before I knew this.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Need to count repeated names only once that meet multiple criteria

    Look at COUNTIFS function,

  5. #5
    Registered User
    Join Date
    12-09-2015
    Location
    england
    MS-Off Ver
    2003
    Posts
    33

    Re: Need to count repeated names only once that meet multiple criteria

    And also in my original post I stated I am using Excel 2003, so countifs is not an option.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Need to count repeated names only once that meet multiple criteria

    I believe SUMPRODUCT would be an alternative.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Need to count repeated names only once that meet multiple criteria

    =SUMPRODUCT(--(C2:C100="TeamName")*--(D2:D100="D")*(E2:E100>=StartDate)*(E2:E100<=enddate))

  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: Need to count repeated names only once that meet multiple criteria

    John, thats not going to count unique names
    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

  9. #9
    Registered User
    Join Date
    12-09-2015
    Location
    england
    MS-Off Ver
    2003
    Posts
    33
    I think ive managed to attach the sample data
    Attached Files Attached Files

  10. #10
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Need to count repeated names only once that meet multiple criteria

    Hi Steeler,

    I believe you can solve this problem using Pivot Tables. They allow filtering and count. They also can group people by teams. Time to learn more about Pivots?

    Read about them at:
    http://www.excelfunctions.net/Advanc...xcel-2003.html
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  11. #11
    Registered User
    Join Date
    12-09-2015
    Location
    england
    MS-Off Ver
    2003
    Posts
    33

    Re: Need to count repeated names only once that meet multiple criteria

    Thank you for your advice on pivot tables. I think this is not a short win though as I'm not experienced on Pivot tables and it's just making this all the more confusing. Can it be done via formula? The bigger picture is there will be lots of different teams and many thousand work returns submitted and I could really do with this auto calculating as I upload the data.

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Need to count repeated names only once that meet multiple criteria

    try this ..

    =SUMPRODUCT(--IFERROR((1/(COUNTIF(A2:A100,A2:A100))),0),--(C2:C100="TEAM")*--(D2:D100="D")*(E2:E100>=G1)*(E2:E100<=G2))

    Enter with Ctrl+Shift+Enter

    G1 and G2 are your dates

  13. #13
    Registered User
    Join Date
    12-09-2015
    Location
    england
    MS-Off Ver
    2003
    Posts
    33

    Re: Need to count repeated names only once that meet multiple criteria

    Thanks John, I've tried this but it's coming back as 0. I've entered it in as an array and adjusted the iferror to if(iserror (as I'm on 2003) and added and additional ) after A100 to account for this.

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Need to count repeated names only once that meet multiple criteria

    =SUMPRODUCT(--(IF(ISERROR(1/COUNTIF(A2:A100,A2:A100)),0,1/COUNTIF(A2:A100,A2:A100))),--(C2:C100="Dept")*--(D2:D100="D")*(E2:E100>=G1)*(E2:E100<=G2))

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Need to count repeated names only once that meet multiple criteria

    My testing ...
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    12-09-2015
    Location
    england
    MS-Off Ver
    2003
    Posts
    33

    Re: Need to count repeated names only once that meet multiple criteria

    Thank you very much, really appreciate all of your help

  17. #17
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Need to count repeated names only once that meet multiple criteria

    Thank for the feedback and apologies from me for not reading your initial posting more carefully.

  18. #18
    Registered User
    Join Date
    12-09-2015
    Location
    england
    MS-Off Ver
    2003
    Posts
    33

    Re: Need to count repeated names only once that meet multiple criteria

    It's all fine John. I will be spending plenty of time here to better my skills. It's great that there are very knowledgeable people like yourself that want to help people like me out. Thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to count the number of rows that meet multiple selection criteria
    By mcnallyb in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-21-2014, 11:26 AM
  2. How to count cells that meet multiple criteria, leaving out duplicates
    By jsgray in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-11-2013, 07:26 AM
  3. Schedule to count multiple lines and columns to meet two criteria
    By lsudecat04 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-28-2013, 05:41 AM
  4. Single count for repeated values with multiple criteria
    By ShwetaGupta in forum Excel General
    Replies: 5
    Last Post: 03-22-2012, 04:48 PM
  5. Using SUM to count cells that meet multiple criteria
    By cricket_stoner in forum Excel General
    Replies: 10
    Last Post: 05-31-2010, 06:01 AM
  6. Count records that meet multiple criteria
    By statenja in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-24-2008, 01:53 AM
  7. how to sum a repeated val only once and meet a certian criteria?
    By DaGersh in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-21-2006, 09:20 PM

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