+ Reply to Thread
Results 1 to 19 of 19

Countif and Countifs help

  1. #1
    Registered User
    Join Date
    03-11-2020
    Location
    Work
    MS-Off Ver
    2016
    Posts
    5

    Countif and Countifs help

    Hi,
    I have used these boards for a while to look for solutions to problems so thank you to everyone who has contributed previously as you have probably helped me before. this is my first post for specific support as I cannot find the answer I'm looking for so maybe I'm looking for the wrong formula.

    I have a database of words related to a topic, for instance B1 is relationship and under is a list of words which correspond, for example "brother, brothers, mother, mothers, father, fathers, sister, sisters" etc.
    In cell A1 I have a statement which says "I have two sisters, five bothers and a mother"

    I would like a formula to count how many words from my relationship range feature in cell a1. I don't need a breakdown of each word, just a sum of relevant words (so in this case I would like the value to return "3".

    I have looked to use LEN(Substitute) formula but don't wish to have to write it for each word in my list, I want it to use the range but I cant get it to work.

    I hope I haven't worded this in a complicated way and thank you for any help you can give me on this.

    Regards,

    Kev.
    Attached Files Attached Files
    Last edited by kevgdob; 03-11-2020 at 08:39 AM. Reason: Added a sample sheet

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,100

    Re: Countif and Countifs help

    I think the biggest problem with formula that brother is a part of brothers etc.
    So I've created UDF. Should works.

    for some unknown reason firewall see SQL injection in this code:
    Capture.JPG
    To use like (in B1):

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    where F1:F20 is a list of your words.


    EDIT:
    small tweak to avoid counting single if only plural exists.

    These 2 lines to replace in org. code.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by KOKOSEK; 03-11-2020 at 09:35 AM.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,247

    Re: Countif and Countifs help

    In B2 copied across

    =SUMPRODUCT((I$2:I$11<>"")*(ISNUMBER(FIND(UPPER(I$2:I$11),UPPER($A2)))))
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,100

    Re: Countif and Countifs help

    Quote Originally Posted by kvsrinivasamurthy View Post
    In B2 copied across

    =SUMPRODUCT((I$2:I$11<>"")*(ISNUMBER(FIND(UPPER(I$2:I$11),UPPER($A2)))))
    False results in case like this:

    A
    B
    C
    D
    1
    QUOTE
    relationship
    Relationship
    2
    I like to go to Spain with my brothers and sisters
    2
    brother
    3
    sister

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,247

    Re: Countif and Countifs help

    Quote Originally Posted by KOKOSEK View Post
    False results in case like this:
    Pl see the file .It is working ok.
    Attached Files Attached Files

  6. #6
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,100

    Re: Countif and Countifs help

    It is from your file. It is wrong.
    In A1 are brothers and sisters and on list are brother & sister.
    So there is no brother or sister in A1 (ignoring facts that brother is one of brothers )

  7. #7
    Registered User
    Join Date
    03-11-2020
    Location
    Work
    MS-Off Ver
    2016
    Posts
    5

    Thumbs up Re: Countif and Countifs help

    Perfect. Thank you both for the replies.
    I have gone with the second one for ease. this is exactly what I wanted.

  8. #8
    Registered User
    Join Date
    03-15-2019
    Location
    sloveni
    MS-Off Ver
    2016
    Posts
    18

    Re: Countif and Countifs help

    Hello,

    i have one problem with formula. I need to count from "Artikel" (Cell A) 325490_L2 how many Ctn. i have (Cell B) but don't count duplicates



    Attachment 666828

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,247

    Re: Countif and Countifs help

    Attachment not opening.

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,033

    Re: Countif and Countifs help

    Hello pernekde. Welcome to the forum.

    Please attach an actual Excel workbook ... not pictures or links. The instructions are at the top of each page in this forum.

    Please upload a small representative workbook sample. The instructions are in the 'gold' banner at the top of this page titled "HOW TO ATTACH YOUR SAMPLE WORKBOOK:" and begin with your name.
    Dave

  11. #11
    Registered User
    Join Date
    03-15-2019
    Location
    sloveni
    MS-Off Ver
    2016
    Posts
    18

    Re: Countif and Countifs help

    Quote Originally Posted by pernekde View Post
    Hello,

    I have one problem with the formula. I need to count from "Artikel" (Cell A) 300236 , 304628 , 304777 and 306184 how many Ctn. i have (Cell B) from each ctn. but don't count duplicates

    Attach of Workbook:
    Attached Files Attached Files
    Last edited by pernekde; 03-11-2020 at 03:27 PM.

  12. #12
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,247

    Re: Countif and Countifs help

    Post deleted
    Last edited by kvsrinivasamurthy; 03-12-2020 at 01:06 AM.

  13. #13
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,247

    Re: Countif and Countifs help

    ARRAY formula in E2 then copy down

    =SUM(IFERROR(1/MMULT(--(IF($A$2:$A$43=$D2,$B$2:$B$43,"")=TRANSPOSE(IF($A$2:$A$43=$D2,$B$2:$B$43," "))),--(ROW($A$2:$A$43)>0)),0))

    How ARRAY formula is entered

    Paste Formula in the cell.
    Press F2
    Hold Shift+ Ctrl Keys and hit Enter key.
    Now the formula is surrounded by {} by excel.
    Attached Files Attached Files

  14. #14
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    Office 365 | 2016
    Posts
    683

    Re: Countif and Countifs help

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    03-15-2019
    Location
    sloveni
    MS-Off Ver
    2016
    Posts
    18

    Re: Countif and Countifs help

    Quote Originally Posted by kvsrinivasamurthy View Post
    ARRAY formula in E2 then copy down

    =SUM(IFERROR(1/MMULT(--(IF($A$2:$A$43=$D2,$B$2:$B$43,"")=TRANSPOSE(IF($A$2:$A$43=$D2,$B$2:$B$43," "))),--(ROW($A$2:$A$43)>0)),0))

    How ARRAY formula is entered

    Paste Formula in the cell.
    Press F2
    Hold Shift+ Ctrl Keys and hit Enter key.
    Now the formula is surrounded by {} by excel.

    Hello

    It works! And if I want to search the whole A und B, not just the first 43 row. How do I change the formula?
    Tnx!!!

  16. #16
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,247

    Re: Countif and Countifs help

    I did not understand. Pl upload a sample file .
    The formula takes both A and B columns.
    What is the maximum row expected?
    Last edited by kvsrinivasamurthy; 03-12-2020 at 09:21 AM.

  17. #17
    Registered User
    Join Date
    03-15-2019
    Location
    sloveni
    MS-Off Ver
    2016
    Posts
    18

    Re: Countif and Countifs help

    Quote Originally Posted by kvsrinivasamurthy View Post
    I did not understand. Pl upload a sample file .
    The formula takes both A and B columns.
    What is the maximum row expected?
    am 20000 i think :=) the excel is to big ...
    Attached Files Attached Files

  18. #18
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,247

    Re: Countif and Countifs help

    For whole column ARRAY formula
    In F2
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 03-12-2020 at 09:48 AM.

  19. #19
    Registered User
    Join Date
    03-15-2019
    Location
    sloveni
    MS-Off Ver
    2016
    Posts
    18

    Re: Countif and Countifs help

    Quote Originally Posted by kvsrinivasamurthy View Post
    For whole column ARRAY formula
    In F2
    Please Login or Register  to view this content.
    Hy,

    its working but i have 20000 row and excel is to slow now its take 1 min to start.... Can we do that different maybe whit VBA ?
    TNX!

+ 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] Help with COUNTIF/ COUNTIFS
    By cjharwood in forum Excel General
    Replies: 3
    Last Post: 11-11-2015, 07:53 AM
  2. [SOLVED] % and COUNTIF/COUNTIFS
    By cjharwood in forum Excel General
    Replies: 3
    Last Post: 09-06-2015, 08:47 PM
  3. [SOLVED] Need help with =COUNTIF or =COUNTIFS
    By portokie in forum Excel General
    Replies: 3
    Last Post: 09-02-2015, 06:16 PM
  4. [SOLVED] Countif or Countifs -
    By MercyMercyMe in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-14-2013, 04:52 PM
  5. Countif and Countifs
    By alexfamily5 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 01-15-2013, 05:51 PM
  6. [SOLVED] How do I use countif or countifs, and how?
    By xodiak in forum Excel General
    Replies: 7
    Last Post: 06-01-2012, 11:10 AM
  7. Countif or countifs - please help
    By Davidcat in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 06-24-2011, 04:53 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