+ Reply to Thread
Results 1 to 6 of 6

Help with Complex COUNT formula

  1. #1
    Registered User
    Join Date
    07-31-2015
    Location
    Georgia
    MS-Off Ver
    2013
    Posts
    18

    Help with Complex COUNT formula

    Hey everyone,

    New to this forum but I'm sure I will be posting more to get help as I work on making my projects a little more automated and easier for me to use. I'm trying to count a number of products on a shelf by using a shelf code or position as a reference.

    Shelf codes are labeled as DF1-1-A which means it is on row 1 shelf 1 position A. So I have a list of products with their relative position but can't figure out how to make it look for every product that is in for example DF10-2-[A-Z] and count the positions only. Any ideas?

    I'm open to using macros as well but I'm trying to avoid them and see if its possible with a function instead.

    Here is some data to work with :

    DF10-1-A 931100477
    DF10-2-A 931100480
    DF10-3-A 931100750
    DF10-3-B 931100808
    DF10-4-A 931100251
    DF10-4-B 931100878
    DF10-5-A 931100817
    DF10-5-B 931100755
    DF10-6-A 931100250
    DF10-6-B 931100811
    DF10-7-A 100105105
    DF10-7-B 100105113
    DF11-1-A 931100279
    DF11-2-A 931100290
    DF11-3-A 931100280
    DF11-4-A 931100239
    DF11-4-B 931100243
    DF11-6-A 931100719
    DF11-7-A 931100241

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Help with Complex COUNT formula

    Try using wildcards:

    For DF10-2-[A-Z]

    =COUNTIF(A:A,"DF10-2-*")

    DF10

    =COUNTIF(A:A,"DF10-*")

    for all -3s

    =COUNTIF(A:A,"*-3-*")
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    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,917

    Re: Help with Complex COUNT formula

    Hi, welcome to trhe forum

    Here is 1 way (Im sure there are others)...
    A
    B
    C
    1
    F10-1-A 931100477
    3
    3
    2
    DF10-2-A 931100480
    3
    DF10-3-A 931100750
    4
    DF10-3-B 931100808
    5
    DF10-4-A 931100251
    6
    DF10-4-B 931100878
    7
    DF10-5-A 931100817
    8
    DF10-5-B 931100755
    9
    DF10-6-A 931100250
    10
    DF10-6-B 931100811
    11
    DF10-7-A 100105105
    12
    DF10-7-B 100105113
    13
    DF11-1-A 931100279
    14
    DF11-2-A 931100290
    15
    DF11-3-A 931100280
    16
    DF11-4-A 931100239
    17
    DF11-4-B 931100243
    18
    DF11-6-A 931100719
    19
    DF11-7-A 931100241

    B1=your selection
    C1=SUMPRODUCT(--(MID(A1:A19,FIND("-",A19,1)+1,1)=TEXT(B1,"0")))

    edit: see, told you, and I think Bernie's may be better
    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

  4. #4
    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,917

    Re: Help with Complex COUNT formula

    Using Bernie's suggestion, change my C1 to this...
    =COUNTIF($A$1:$A$19,"*-"&B1&"-*")

  5. #5
    Registered User
    Join Date
    07-31-2015
    Location
    Georgia
    MS-Off Ver
    2013
    Posts
    18

    Re: Help with Complex COUNT formula

    Thanks for the suggestions guys. Got me thinking and I did a little modification of my own for the formula used above with a LEFt function to make sure nothing gets missed in the position ultimately just cutting of the letter position to get the count. I did have an issue with some of the lower numbered positions like DF1-1-A since it only had a single number rather than a double like DF10 this was not working. I'm changing my positions to DF01 etc to fix.

    This will save me lots of time in the long run. Much appreciated for the quick responses.

  6. #6
    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,917

    Re: Help with Complex COUNT formula

    The suggested countif - with a reference - would do the count for and number of characters

+ 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] complex count formula needed
    By Sam Capricci in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 07-17-2015, 07:37 PM
  2. [SOLVED] Challenging complex formula- unique count for multiple criteria on several levels
    By joannelittell in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-30-2013, 09:52 PM
  3. complex count formula?
    By Cheymeister in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-01-2013, 04:30 PM
  4. Complex count if ?!
    By _Lewis in forum Excel General
    Replies: 4
    Last Post: 06-14-2011, 08:17 AM
  5. Complex multi criteria lookup and count formula???
    By JapanDave in forum Excel General
    Replies: 9
    Last Post: 05-25-2009, 04:36 AM
  6. Formula Complex with IF and Count
    By Sultix in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-29-2008, 07:39 PM
  7. [SOLVED] complex count
    By FSmitty in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-03-2006, 06:25 PM

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