+ Reply to Thread
Results 1 to 9 of 9

Help Counting Cells w/first 3 letters...

  1. 12-30-2012, 05:20 PM #1
    CRichardsDSX
    CRichardsDSX is offline
    Registered User
    Join Date
    12-30-2012
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    3

    Help Counting Cells w/first 3 letters...

    Hello All,

    I've seen similar posts and have tried to apply those concepts - but have not yet come up with one that's worked.

    I have a series of data like:

    abc1 3
    abc2 4
    abc3 0
    xyz1 1
    xyz2 0
    xyz3 0
    ...

    There's about 50 different 3-letter prefixes that I'd like to count and display in a table. I've tried the following without success:

    =COUNTIFS(Data!D:D,(LEFT(Data!D:D,3))=Graphs!P35,Data!R:R,"<>0")

    Where "Data!D:D" is the raw data, "Graphs!P##" is my table of prefixes, and "Data!R:R" is the number of items (only counts non-zero).

    I feel like maybe I'm close - I've been staring at this for a few hours and am surely missing something simple I hope..

    Any help is greatly appreciated! Thanks!!
    Reply With QuoteRegister To Reply
  2. 12-30-2012, 05:29 PM #2
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help Counting Cells w/first 3 letters...

    I should solve it like this (with an pivot table).
    Attached Files Attached Files
    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.
    Reply With QuoteRegister To Reply
  3. 12-30-2012, 05:36 PM #3
    FDibbins
    FDibbins is online now
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Help Counting Cells w/first 3 letters...

    if you can live with a helper column, you could extract the 1st 3 digits, and then base your search on that? the helper can be hidden if needed
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  4. 12-30-2012, 05:42 PM #4
    CRichardsDSX
    CRichardsDSX is offline
    Registered User
    Join Date
    12-30-2012
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Help Counting Cells w/first 3 letters...

    Thanks to both of you!!!

    FDibbins - I'll take the helper column approach... cleaner in the long run than what I was trying to do. I would still love to know where I went wrong in my formula though... it'll bug me :-)

    Thanks again!
    Reply With QuoteRegister To Reply
  5. 12-30-2012, 06:51 PM #5
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,428

    Re: Help Counting Cells w/first 3 letters...

    A little late to the game but ...

    =SUMPRODUCT(--(LEFT($D$1:$D$6,3)="abc"))

    or:

    =SUMPRODUCT(--(LEFT($D$1:$D$6,3)=Graphs!P35))


    Note that, with SUMPRODUCT, you must specify a range and not a whole column. Although this is allowed in Excel 2007 and above, it is not recommended.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  6. 12-30-2012, 09:46 PM #6
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help Counting Cells w/first 3 letters...

    Try it like this...

    =COUNTIFS(Data!D:D,Graphs!P35&"*",Data!R:R,"<>0")
    Last edited by jeffreybrown; 12-30-2012 at 09:51 PM. Reason: As per Forum Rule #12, please don't quote whole posts unless necessary -- it's just clutter.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  7. 12-31-2012, 08:11 AM #7
    CRichardsDSX
    CRichardsDSX is offline
    Registered User
    Join Date
    12-30-2012
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Help Counting Cells w/first 3 letters...

    Thanks Tony! The wildcard worked!
    Reply With QuoteRegister To Reply
  8. 12-31-2012, 09:40 AM #8
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help Counting Cells w/first 3 letters...

    You're welcome. Thanks for the feedback!
    Reply With QuoteRegister To Reply
  9. 12-31-2012, 09:47 AM #9
    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: Help Counting Cells w/first 3 letters...

    @ Admin-Moderators.

    My opinion is that a Microsoft MVP, COULD NOT BE just A "Registered User" in our Forum.

    A different HONORARY TITLE?
    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
+ 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