+ Reply to Thread
Results 1 to 7 of 7

Counting a row if a word repeats 3 or more times in the cells of that row.

  1. #1
    Registered User
    Join Date
    05-04-2020
    Location
    Falkirk, Scotland
    MS-Off Ver
    Office 365
    Posts
    3

    Counting a row if a word repeats 3 or more times in the cells of that row.

    Hello, new member here & I've had a 5 year break from using Excel, but my job now calls for extensive use of it, so learning all I can.

    I’m hoping someone could point me in the right direction with this one (I've tried searching, but I'm not sure what I'm looking for to put in a search).

    What I’m wanting to do is show how many children are in each school that have 3 or more “High” ASN needs. I'll also be doing it with mix of "High" & "Medium". For the "High" example, I want to count rows 2, 5, 11 & 13 for Bass Primary. Row 9 for Drum Academy. Row 8 for Synth Secondary.

    So I know I want to check columns D-H and count “High” in each row, then if it shows 3 or more times for the school, count it as 1. I dare say this is a simple formula, but I'm struggling with where to start.

    I've attached a spreadsheet...
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Counting a row if a word repeats 3 or more times in the cells of that row.

    There is probably an easier way to do this, I have used helper columns to create the instances where High is greater than 3 and then used SUMIF to group
    Maybe a pivot table would work better

    Anyway
    the name BASS PRIMARY is not all the Same in the cells , not sure if a space or special character
    But if you use duplicates you get Bass Primary listed twice
    SO i copied the first name into the other cells to remove this error

    see attached

    as i say, i suspect a grid count maybe possible - just not come to me yet
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    05-04-2020
    Location
    Falkirk, Scotland
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Counting a row if a word repeats 3 or more times in the cells of that row.

    Thanks.

    After much messing about, I think it needs to be a 2 stage affair. I've added 3 columns named High Count, Medium Count & Low Count & used COUNTIF on them. I've then on a new sheet put the school headings & in the cell below each, I've used COUNTIFS to get the info.

    I'll probably be back soon for help with charts & slicers (& possibly pivot tables too). Lol.

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Counting a row if a word repeats 3 or more times in the cells of that row.

    Now I see it is pretty much similar to Etaf's solution.
    I will post it anyway -

    Helper in column I to find the number of times "HIGH" appears , and the formula below near the reference of the school name:

    =COUNT(IF(($C$1:$C$16=$C20)*($I$1:$I$16=3),$I$1:$I$16))
    Attached Files Attached Files
    Last edited by Limor_OP; 05-04-2020 at 08:52 AM.

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Counting a row if a word repeats 3 or more times in the cells of that row.

    that was pretty much what i posted in the sample
    ONLY I used a Countif to see if 3 or more
    and then a SUMIF to sum where there are 3 or more instances

    Just make sure the school name is identical as I posted, the Bass Primary is different and not all the same
    Last edited by etaf; 05-04-2020 at 08:36 AM.

  6. #6
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Counting a row if a word repeats 3 or more times in the cells of that row.

    And here without a helper:

    =SUMPRODUCT(--(FREQUENCY(((C2:C16=C20)*(D2:H16="High"))*(ROW(C2:C16)-ROW(C2)+1),ROW(C2:C16)-ROW(C2))=3))

    If you want 3 Mediums in your counting - you will have to change the BOLD condition to "Medium"
    Attached Files Attached Files

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Counting a row if a word repeats 3 or more times in the cells of that row.

    Please try

    =FILTER(A2:C16,MMULT(N(D2:H16="high"),TRANSPOSE(COLUMN(D2:H16))^0)>2)
    Attached Files Attached Files

+ 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. [SOLVED] Counting How many times a word apears in a range
    By mul3am in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-18-2017, 05:28 PM
  2. Counting the number of times a word appears with a twist
    By laurigardner in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-02-2017, 12:37 AM
  3. Macro to return cells if cell repeats value over 5 times in a list
    By hteaford in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-01-2016, 08:36 AM
  4. Counting number of times a word appears across worksheets
    By danltd in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-24-2012, 04:33 AM
  5. Replies: 5
    Last Post: 02-03-2012, 07:05 AM
  6. Replies: 19
    Last Post: 05-26-2011, 04:15 PM
  7. Counting the number of times a word appears 'anywhere' on a page
    By Brother Laz in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 06-17-2006, 10:10 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