+ Reply to Thread
Results 1 to 11 of 11

COUNTIF with different names

  1. #1
    Registered User
    Join Date
    11-15-2011
    Location
    Scotland
    MS-Off Ver
    Mac Excel 2008
    Posts
    6

    COUNTIF with different names

    So
    I'm doing some athlete research and hit a bump, I need to:

    Count the number of names swimmers from Column F that match the names of swimmers on Column K if the swimmers from column F have a ranking of less than 100 (rankings are listed on column A).

    Any idea on how i do this?
    Last edited by Zaad; 11-15-2011 at 12:32 AM.

  2. #2
    Registered User
    Join Date
    11-15-2011
    Location
    Scotland
    MS-Off Ver
    Mac Excel 2008
    Posts
    6

    Re: Need help with this counting thing

    I know its got something to do with COUNTIFS but i dont know what to do...

    =COUNTIFS('2005 Male'!$A$8:$A$157, "<=100",

    I need to do this for about 20 lists

    ** APOLOGIES! CHANGING THREAD TITLE!
    Last edited by Zaad; 11-15-2011 at 12:30 AM.

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: COUNTIF with different names

    Try

    =SUMPRODUCT((A8:A157<=100)*(F8:F157=K8:K157))
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Registered User
    Join Date
    11-15-2011
    Location
    Scotland
    MS-Off Ver
    Mac Excel 2008
    Posts
    6

    Re: COUNTIF with different names

    Quote Originally Posted by Special-K View Post
    Try

    =SUMPRODUCT((A8:A157<=100)*(F8:F157=K8:K157))
    Hmm...
    its not working.
    it doesnt seem to process it.

    im using excel on Mac if that makes a difference?

  5. #5
    Registered User
    Join Date
    11-15-2011
    Location
    Scotland
    MS-Off Ver
    Mac Excel 2008
    Posts
    6

    Re: COUNTIF with different names

    To make it easier for you guys to answer, heres the work book.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor Miraun's Avatar
    Join Date
    04-03-2009
    Location
    New England
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    554

    Re: COUNTIF with different names

    3 Things:

    1: The entire worksheet is formatted as Text. As a result, when you type in an equation into column L, it'll just hang out there as text. You need to format the cells as General to properly use equations.

    2: If all of your worksheets are formatted from 1st place to 100th place, you can simply cut off the range that you search through rather than using a more complex, two variable statement.

    =COUNTIF($F$4:$F$103,K4)

    Counts the instances of the swimmer's name in K4 compared to the listing in the top 100 on that given worksheet.

    3: Countifs is a 2007 function, you can't use it in 2003. If you're using 2003 like your profile states, you're stuck to Countif, or an array function with IF statements.
    Going for Guru! Click the Star to the bottom left of this post if I helped!

  7. #7
    Registered User
    Join Date
    11-15-2011
    Location
    Scotland
    MS-Off Ver
    Mac Excel 2008
    Posts
    6

    Re: COUNTIF with different names

    Quote Originally Posted by Miraun View Post
    3 Things:

    1: The entire worksheet is formatted as Text. As a result, when you type in an equation into column L, it'll just hang out there as text. You need to format the cells as General to properly use equations.
    Oops, thanks!

    2: If all of your worksheets are formatted from 1st place to 100th place, you can simply cut off the range that you search through rather than using a more complex, two variable statement.

    =COUNTIF($F$4:$F$103,K4)

    Counts the instances of the swimmer's name in K4 compared to the listing in the top 100 on that given worksheet.
    The full worksheet is much larger. i didnt want to upload it as its lots of data. my bandwidth cant handle it.
    its a 7000 row spread sheet and i have 20 of these.
    its a lot of data with one list below the other
    i want to be able to have a formula i can use on every list separately (just fill in the cell ranges on the formula) and something that i could use to search the top100, top 50 as well as the top 25


    3: Countifs is a 2007 function, you can't use it in 2003. If you're using 2003 like your profile states, you're stuck to Countif, or an array function with IF statements.
    Apologies, i updated my profile earlier but it didnt change, i use Mac Excel 2008
    Thanks for your help but (read the above)

  8. #8
    Registered User
    Join Date
    11-15-2011
    Location
    Scotland
    MS-Off Ver
    Mac Excel 2008
    Posts
    6

    Re: COUNTIF with different names

    Anyone?
    Anyone got any idea?

  9. #9
    Valued Forum Contributor Miraun's Avatar
    Join Date
    04-03-2009
    Location
    New England
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    554

    Re: COUNTIF with different names

    No worries, I'm still here...

    I should have realized when you attached an xlsx file that you weren't working off of 2003!

    You were SOOO close with your original countifs statement:
    =COUNTIFS(A:A,"<101",F:F,K4)
    Basically, it's Range 1, Condition 1, Range 2, condition 2.
    I'm using the full columns as ranges, but you can tailor them to whatever the range actually is within your worksheet.

  10. #10
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: COUNTIF with different names

    Hi Zaad
    the attachment may be helpful
    TEST(1).xlsx
    Azam
    If you want to say Thank you to a member, click the reputation icon (Star) in the left bottom of the post.
    For prompt answer, be descriptive, concise, short, direct, and to-the-point.

  11. #11
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: COUNTIF with different names

    also try
    =SUMPRODUCT(COUNTIFS($F$4:$F$153,$K$4:$K$10,$A$4:$A$153,"<=100"))
    or
    =SUMPRODUCT(COUNTIFS($F:$F,$K$4:$K$10,$A:$A,"<=100"))
    Last edited by Azam Ali; 11-15-2011 at 05:30 AM.

+ 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