+ Reply to Thread
Results 1 to 12 of 12

Formula for counting text values in column when value is repeated in cells

  1. 12-11-2012, 11:11 AM #1
    Blubirdjim
    Blubirdjim is offline
    Registered User
    Join Date
    12-11-2012
    Location
    Swindon, England
    MS-Off Ver
    Excel 2007
    Posts
    15

    Formula for counting text values in column when value is repeated in cells

    In the uploaded workbook I have 2 active sheets. Sheet1 and Calling Sheet.

    In "Calling sheet" Column Y lists the results of calling activity.

    On "Sheet1" there are 4 cells B1:B4 I need a formula that will count the initials of the staff members in the notes in they column y cells in the "Calling Sheet". So Mark Weaver is MW, Jim Saunders is JS....

    I need a formula that will count the amount of times the initials of the listed people appear in the notes in column y. Bear in mind that in each cell the initials can be entered multiple times on new "Alt&Enter" lines.

    So in conclusion I really need some help here!!
    Attached Files Attached Files
    Last edited by Blubirdjim; 12-11-2012 at 12:25 PM.
    Reply With QuoteRegister To Reply
  2. 12-11-2012, 11:15 AM #2
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Formula stops working after 77 lines?????? HEEELLLLPPPP! :o)

    Hi

    1) Please change your title according the forum rules.

    2) If a formula works for 1 cell, then works for 1.000.000 more!

    3) Upload a sample workbook to see how your data look like!
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  3. 12-11-2012, 11:26 AM #3
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,852

    Re: Formula for counting text values in column when value is repeated in cells

    Put "JS" in C1 and "MW" in D1 (without the quotes), then you can put this array* formula in C2:

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


    and this can be copied to D2, to give you the results of 9 and 4 respectively from your sample data.

    *An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual <Enter>

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  4. 12-11-2012, 11:46 AM #4
    Blubirdjim
    Blubirdjim is offline
    Registered User
    Join Date
    12-11-2012
    Location
    Swindon, England
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Formula for counting text values in column when value is repeated in cells

    I tried this array formula before, but it seems to stop working after 77 or so lines with a #name? error
    Reply With QuoteRegister To Reply
  5. 12-11-2012, 11:53 AM #5
    Blubirdjim
    Blubirdjim is offline
    Registered User
    Join Date
    12-11-2012
    Location
    Swindon, England
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Formula for counting text values in column when value is repeated in cells

    This only works for about 78 rows. I need it to read a whole column.
    Last edited by Blubirdjim; 12-11-2012 at 12:05 PM.
    Reply With QuoteRegister To Reply
  6. 12-11-2012, 12:06 PM #6
    Blubirdjim
    Blubirdjim is offline
    Registered User
    Join Date
    12-11-2012
    Location
    Swindon, England
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Formula for counting text values in column when value is repeated in cells

    If someone can look at the workbook I uploaded and make it work I would be chuffed cos I have failed to find anyone that can do it.
    Reply With QuoteRegister To Reply
  7. 12-11-2012, 12:10 PM #7
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,852

    Re: Formula for counting text values in column when value is repeated in cells

    Instead of $A1:$A4 you can write it as $A:$A for a full column (twice in the formula), although this will take the formula a long time to calculate - you should make the ranges in array formulae just long enough to encapsulate your data.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  8. 12-11-2012, 12:15 PM #8
    Blubirdjim
    Blubirdjim is offline
    Registered User
    Join Date
    12-11-2012
    Location
    Swindon, England
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Formula for counting text values in column when value is repeated in cells

    Hi Pete, I tried this and it did not work. I do need the formula to be on sheet1 and the data to be on "calling Sheet" Can you upload my sheet and see for yourself?
    Reply With QuoteRegister To Reply
  9. 12-11-2012, 12:23 PM #9
    Blubirdjim
    Blubirdjim is offline
    Registered User
    Join Date
    12-11-2012
    Location
    Swindon, England
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Formula for counting text values in column when value is repeated in cells

    I will re-upload the sheet
    Reply With QuoteRegister To Reply
  10. 12-11-2012, 12:26 PM #10
    Blubirdjim
    Blubirdjim is offline
    Registered User
    Join Date
    12-11-2012
    Location
    Swindon, England
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Formula for counting text values in column when value is repeated in cells

    I have re-uploaded the sheet with the formula in it and it should show the problem I have.
    Reply With QuoteRegister To Reply
  11. 12-11-2012, 12:33 PM #11
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,852

    Re: Formula for counting text values in column when value is repeated in cells

    In the attached file, I've put the formulae in column C.

    The problem you had was that the #NAME error appeared in cell Y79 of the Calling sheet - I just deleted that entry.

    Hope this helps.

    Pete
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 12-11-2012, 12:36 PM #12
    Blubirdjim
    Blubirdjim is offline
    Registered User
    Join Date
    12-11-2012
    Location
    Swindon, England
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Formula for counting text values in column when value is repeated in cells

    You are a legend Pete!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1