+ Reply to Thread
Results 1 to 15 of 15

Please Help. Difficulty with COUNTIF formula for multiple ranges.

  1. #1
    Registered User
    Join Date
    12-05-2020
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    27

    Question Please Help. Difficulty with COUNTIF formula for multiple ranges.

    Hello.

    As you'll be able to gather, I am no excel expert, but I do enjoy learning new excel formulas. I hope someone will be able to help me with this one.
    I have been running a private fantasy football (soccer for many) league for many years using excel to document the league table. Each month we have awards for managers that achieve the highest monthly total. I am now trying to create a tally for each manager on how many monthly awards they have had. Attached is a rough example of what my data looks like. In B3:B13 I would like a formula to calculate how many times manager names appears in cells G16, K16, R16, U16, X16 & AB16. Ordinally this would be simple formula, but I need it to be a partial match because occasionally two names appear for when it is a tie. Also, in my original document these cell references are in another workbook, so I get a #Value! error when I close the associated workbook.
    I do hope this makes sense and that someone can help.

    Many Thanks

    Charlie
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Please Help. Difficulty with COUNTIF formula for multiple ranges.

    You can use this formula in B3:

    =COUNTIFS(Sheet2!$16:$16,"*"&A3&"*",Sheet2!$4:$4,"Total")

    It just counts the names for the columns headed Total, so you don't need to specifically denote those columns.

    Hope this helps.

    Pete

  3. #3
    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
    27,992

    Re: Please Help. Difficulty with COUNTIF formula for multiple ranges.

    Try

    =SUMPRODUCT((Sheet2!$B$16:$AB$16=$A3)*(Sheet2!$B$4:$AB$4="Total"))
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  4. #4
    Registered User
    Join Date
    12-05-2020
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    27

    Re: Please Help. Difficulty with COUNTIF formula for multiple ranges.

    Quote Originally Posted by Pete_UK View Post
    You can use this formula in B3:

    =COUNTIFS(Sheet2!$16:$16,"*"&A3&"*",Sheet2!$4:$4,"Total")

    It just counts the names for the columns headed Total, so you don't need to specifically denote those columns.

    Hope this helps.

    Pete
    Thanks for the reply Pete.

    Unfortunately this formula didn't work for me. I got a value of 0 for all cells (B3:B13).

    Charlie

  5. #5
    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
    27,992

    Re: Please Help. Difficulty with COUNTIF formula for multiple ranges.

    Pete's formula worked for me so error at your end!!!
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-05-2020
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    27

    Re: Please Help. Difficulty with COUNTIF formula for multiple ranges.

    Quote Originally Posted by JohnTopley View Post
    Try

    =SUMPRODUCT((Sheet2!$B$16:$AB$16=$A3)*(Sheet2!$B$4:$AB$4="Total"))
    Thanks John this worked a treat! Every day is a school day.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Please Help. Difficulty with COUNTIF formula for multiple ranges.

    Here's the file that I put the formula in. How is it different to yours?

    Pete
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-05-2020
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    27

    Re: Please Help. Difficulty with COUNTIF formula for multiple ranges.

    Quote Originally Posted by Pete_UK View Post
    Here's the file that I put the formula in. How is it different to yours?

    Pete
    Yes it works now. I am not sure what happened. Both work great. Thanks gents!

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Please Help. Difficulty with COUNTIF formula for multiple ranges.

    The main differences between my formula and John's is that he uses a specific range (up to column AB), whereas mine doesn't (so you can add more data without needing to change the formula), and also John's formula is looking for an exact match with the name, whereas mine is looking for a partial match (so that if you had two names on a tie in one of the Total columns then they would both be picked up). A drawback with this latter approach is that if you had a name which was a subset of another's name (e.g. Jo and John), then Jo would also pick up John's count.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  10. #10
    Registered User
    Join Date
    12-05-2020
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    27

    Re: Please Help. Difficulty with COUNTIF formula for multiple ranges.

    Quote Originally Posted by Pete_UK View Post
    The main differences between my formula and John's is that he uses a specific range (up to column AB), whereas mine doesn't (so you can add more data without needing to change the formula), and also John's formula is looking for an exact match with the name, whereas mine is looking for a partial match (so that if you had two names on a tie in one of the Total columns then they would both be picked up). A drawback with this latter approach is that if you had a name which was a subset of another's name (e.g. Jo and John), then Jo would also pick up John's count.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete
    Thanks for the clear explanation and I have now used your method with my original spreadhseet.
    However, I have encountered an issue. The data source is on another workbook so when I close that workbook I get the dreaded #VALUE! error. I've had this issue before when using the countif formula. I was able to solve the issue by changing the formula to =COUNT(IF instead of COUNTIF (found this technique online). However, I am not sure if I am able to do this with this new formula.
    Can you help?

    Thanks

    Charlie

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Please Help. Difficulty with COUNTIF formula for multiple ranges.

    The simplest approach would be to keep the structure that you have in your sample workbook, i.e. to add the (simpler) summary sheet to your other workbook. Is there some reason why you don't want to do that?

    COUNTIF doesn't work very well with external workbooks, so if you insist on keeping the files separate then it would be better to use John's formula with SUMPRODUCT. Instead of just the sheet references in that formula, though, you would need to use the full path to the file and to include the filename (with extension) in square brackets. You should also include apostrophes around the path/filename/sheet references. I don't know how you have set up your hard-disc, so I can't tell you exactly what it should be.

    There is no great advantage in using the COUNT(IF construct in this set-up - SUMPRODUCT will work just as well, although bear in mind what I said earlier about being able to pick both names in the situation of a tie. You may need to use an ISNUMBER/SEARCH combination instead of an exact match.

    Hope this helps.

    Pete

  12. #12
    Registered User
    Join Date
    12-05-2020
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    27

    Re: Please Help. Difficulty with COUNTIF formula for multiple ranges.

    Quote Originally Posted by Pete_UK View Post
    The simplest approach would be to keep the structure that you have in your sample workbook, i.e. to add the (simpler) summary sheet to your other workbook. Is there some reason why you don't want to do that?

    COUNTIF doesn't work very well with external workbooks, so if you insist on keeping the files separate then it would be better to use John's formula with SUMPRODUCT. Instead of just the sheet references in that formula, though, you would need to use the full path to the file and to include the filename (with extension) in square brackets. You should also include apostrophes around the path/filename/sheet references. I don't know how you have set up your hard-disc, so I can't tell you exactly what it should be.

    There is no great advantage in using the COUNT(IF construct in this set-up - SUMPRODUCT will work just as well, although bear in mind what I said earlier about being able to pick both names in the situation of a tie. You may need to use an ISNUMBER/SEARCH combination instead of an exact match.

    Hope this helps.

    Pete
    I stayed away from creating a simpler summary sheet because I didn't think it was necessary and that one formula would be quicker and less hassle. However, I'll consider creating a summary sheet now.
    How would the sumproduct formula look, including ISNUMBER/SEARCH combination?

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Please Help. Difficulty with COUNTIF formula for multiple ranges.

    In the original sample file, you could use this amendment to John's formula:

    =SUMPRODUCT((ISNUMBER(SEARCH($A3,Sheet2!$B$16:$AB$16)))*(Sheet2!$B$4:$AB$4="Total"))

    This will look for the name appearing anywhere within the Total cells on row 16 (but only for columns B to AB - adjust these ranges to suit as you add data.

    Hope this helps.

    Pete

  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
    27,992

    Re: Please Help. Difficulty with COUNTIF formula for multiple ranges.

    @Pete_UK: Thank you

  15. #15
    Registered User
    Join Date
    12-05-2020
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    27

    Re: Please Help. Difficulty with COUNTIF formula for multiple ranges.

    Quote Originally Posted by Pete_UK View Post
    In the original sample file, you could use this amendment to John's formula:

    =SUMPRODUCT((ISNUMBER(SEARCH($A3,Sheet2!$B$16:$AB$16)))*(Sheet2!$B$4:$AB$4="Total"))

    This will look for the name appearing anywhere within the Total cells on row 16 (but only for columns B to AB - adjust these ranges to suit as you add data.

    Hope this helps.

    Pete
    Thanks, this worked perfectly.

+ 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. countif over multiple ranges?
    By guthrie in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 07-12-2018, 09:50 AM
  2. [SOLVED] multiple ranges in countif formula
    By Losguapos1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-18-2013, 01:51 PM
  3. [SOLVED] countif function difficulty
    By kilomanjaro4 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-19-2013, 10:42 AM
  4. [SOLVED] trying to do a "countif" with multiple ranges and multiple criteria. Countif, Sumproduct?
    By completelyhis in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-07-2013, 06:12 PM
  5. Replies: 7
    Last Post: 07-19-2012, 10:22 AM
  6. Formula COUNTIF Multiple Ranges with One Criteria
    By katcollins in forum Excel General
    Replies: 27
    Last Post: 01-11-2012, 04:21 PM
  7. Difficulty in Countif or Sumproduct function
    By Grimace in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-06-2009, 02:39 AM

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