+ Reply to Thread
Results 1 to 15 of 15

Count the number of cells with 3 or less characters but match against criteria

  1. #1
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    MS 2016
    Posts
    599

    Count the number of cells with 3 or less characters but match against criteria

    Hi guys,

    I have sheet 1 and I am trying to count the NUMber of cells with 3 or less characters, but this has to be matched by the country on both sheets

    so I have a country in Cell A2, sheet2, and this country exists within coulmn G on sheet 1. I want to have a LEN or something formula please to count the number of cells in column H that have 3 or less characters. Also, I have stripped down the data in my spreadsheet, so this should included column I on sheet1 which has this: GeneralCommentsMy original spreadsheet has multiple filters, so I need the number of cells in column H from sheet 1 which have 3 or less words whilst matching the country and being for the filter GeneralComments. Thank you guys!

    sheet attached
    Attached Files Attached Files
    Last edited by rayted; 08-20-2020 at 12:49 PM.
    Thanks,

    R.



  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Count the number of cells with 3 or less characters but match against criteria

    Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    MS 2016
    Posts
    599

    Re: Count the number of cells with 3 or less characters but match against criteria

    Hi oeldere, added more content, sorry i have updated the Original post, I need the count of 3 or less characters, not 3 or less words, which are relevant to relevant country and filter GeneralComments

    I am trying a counit at present with no success
    Attached Files Attached Files
    Last edited by rayted; 08-20-2020 at 12:57 PM.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Count the number of cells with 3 or less characters but match against criteria

    Where do I find the manualy added expected results?

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,500

    Re: Count the number of cells with 3 or less characters but match against criteria

    In A2 copied right
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  6. #6
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    MS 2016
    Posts
    599

    Re: Count the number of cells with 3 or less characters but match against criteria

    sorry attached
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,876

    Re: Count the number of cells with 3 or less characters but match against criteria

    How about
    =SUMPRODUCT((Sheet1_!$G$2:$G$13=A1)*(LEN(Sheet1_!$H$2:$H$13)<=3))

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

    Re: Count the number of cells with 3 or less characters but match against criteria

    Please try

    =COUNTIFS(Sheet1_!$G:$G,A1,Sheet1_!$H:$H,"<>????*")

  9. #9
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    MS 2016
    Posts
    599

    Re: Count the number of cells with 3 or less characters but match against criteria

    Hi guys, thank you for your solutions but this does not factor in column I and the filter/other criteria being General Comments

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,876

    Re: Count the number of cells with 3 or less characters but match against criteria

    Missed the col I part, how about
    =SUMPRODUCT((Sheet1_!$G$2:$G$13=A1)*(LEN(Sheet1_!$H$2:$H$13)<=3)*(Sheet1_!$I$2:$I$13="GeneralComments"))

  11. #11
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    MS 2016
    Posts
    599

    Re: Count the number of cells with 3 or less characters but match against criteria

    AMAZING TY all and fluff13!

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,876

    Re: Count the number of cells with 3 or less characters but match against criteria

    You're welcome & thanks for the feedback.

  13. #13
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    MS 2016
    Posts
    599

    Re: Count the number of cells with 3 or less characters but match against criteria

    Hi Fluff13.. sorry....

    Can you check this out.. I have applied the formula successfully to my real data. I am just trying to verify the figures from the formula.

    And there are 17 rows (which is the figure I want)
    but the formula is showing 32 (because it is summing the number of characters)............

    1
    3
    3
    1
    3
    3
    1
    2
    1
    1
    3
    1
    3
    1
    2
    2
    1

    I would like to know how many rows (17 in this case) have 3 or less characters in cell H. Can you update/help with formula please? Does a countproduct instead of sum product exist?

  14. #14
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,876

    Re: Count the number of cells with 3 or less characters but match against criteria

    The formula does sum the number of characters, it sums the number of rows that match the criteria

  15. #15
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    MS 2016
    Posts
    599

    Re: Count the number of cells with 3 or less characters but match against criteria

    ok thanks, got it!

+ 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] Count Number Of Single Non-Blank Cells Not Containing Characters
    By swordswinger710 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 9
    Last Post: 03-26-2019, 09:29 AM
  2. count the same number of characters between two or three cells and update the remaining
    By perus josh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-14-2018, 03:45 PM
  3. Replies: 4
    Last Post: 11-01-2016, 03:28 PM
  4. Replies: 4
    Last Post: 09-03-2015, 06:20 PM
  5. [SOLVED] Count cells where characters 2 to 8 is a number
    By DD1 in forum Excel General
    Replies: 8
    Last Post: 08-16-2015, 09:17 PM
  6. Replies: 1
    Last Post: 11-20-2014, 06:21 AM
  7. How to count characters in a number of cells?
    By Staalander in forum Excel General
    Replies: 8
    Last Post: 01-11-2007, 11:36 AM

Tags for this Thread

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