+ Reply to Thread
Results 1 to 4 of 4

Countif: Same word counting

  1. #1
    Registered User
    Join Date
    04-11-2020
    Location
    stoke
    MS-Off Ver
    -
    Posts
    9

    Countif: Same word counting

    Hi there

    I'm aware about how to count certain words in a range of cells, however some words appear in the same cell as it not being counted as part of the overall total.

    is there a way in which i can count every time a certain cell is being used?

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Countif: Same word counting

    Hi tkred,

    I think you are looking something like: Replace the "word" in the cell with "", then see the length of the cell before and after. This is how we count how many times a string is in a cells worth of words.

    Not completely understanding your question, this might also give you a tool: https://exceljet.net/formula/count-c...-specific-text
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    04-11-2020
    Location
    stoke
    MS-Off Ver
    -
    Posts
    9

    Re: Countif: Same word counting

    Hi MarvinP


    Basically lets say I want to count how many times the word Summer is included in every cell from A1:A5 (so 5 cells) However a3 has the word Summer in the cell about 3 times meaning the total amount should be (8), however the amount totals to 5. I want to make sure it counts the overall amount the word is use and not the amount of cells has the world included.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Countif: Same word counting

    As illustrated in the attached file, it could be done using some helper columns (C:F) populated using: =IFERROR(SEARCH($C$1,$A2,IF(COLUMN()=3,1,B2)+6*(COLUMNS($A$1:A$1)-1)),"")
    The count is displayed using: =COUNT(C2:F6)
    Let us know if you have any questions.

    Edit: Update the formula in C2 (over and down to F6) to read: =IFERROR(SEARCH($C$1,$A2,IF(COLUMN()=3,1,B2)+LEN($C$1)*(COLUMNS($A$1:A$1)-1)),"")
    Attached Files Attached Files
    Last edited by JeteMc; 04-17-2020 at 07:44 PM. Reason: Updated formula
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Counting with COUNTIF
    By sonky in forum Excel General
    Replies: 2
    Last Post: 06-30-2018, 11:02 PM
  2. [SOLVED] Countif? Counting #s above a cell
    By audax48 in forum Excel General
    Replies: 3
    Last Post: 06-13-2016, 04:12 PM
  3. [SOLVED] CountIf not counting...
    By ciresuark in forum Excel General
    Replies: 2
    Last Post: 01-20-2015, 05:43 PM
  4. [SOLVED] Formula Query - COUNTIF? Column A contains word and Column B contains word
    By HelenJH in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-14-2013, 11:23 AM
  5. [SOLVED] COUNTIF not counting
    By alb43 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-16-2013, 08:11 AM
  6. [SOLVED] Counting of Specific Word and higlight that word
    By Kumail Rizvi in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 10-19-2012, 11:34 PM
  7. Replies: 14
    Last Post: 07-13-2005, 07:05 PM

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