Consulting

Results 1 to 9 of 9

Thread: Sumproduct instead of countif. 1 digit numbers problem.

  1. #1
    VBAX Newbie
    Joined
    Aug 2013
    Posts
    4
    Location

    Sumproduct instead of countif. 1 digit numbers problem.

    Hello, VBA express!
    Firstly, I'd like to thank you all for the helpful tips I've found on this forum so far, keep it up!
    However, there is one thing that I am yet to find a solution for

    Here is the deal: I've got a data set with numbers, let's say:

    A1
    1 325
    2 562
    3 0
    4 95
    5 568
    6 327
    7 182
    8 962
    9 168
    10 207

    When I need to find the number of times cells in A1:A10 start with 5, I use:
    =SUMPRODUCT((LEFT(A1:A10)+0=5)+0) //this is the simplest formula I could find
    and it works great, it returns "2", just as it should, meaning that there are 2 cells that start with 5.

    But, if I need a count of the number of times the second digit is 6 and use this formula:
    =SUMPRODUCT((MID(A1:A10,2,1)+0=6)+0)
    it returns a #VALUE! error, as there are cells in this range that contain 1 digit only.

    I could get around this problem by taking a sum of the following formulas:
    =SUMPRODUCT((LEFT(A1:A10,2)+0=16)+0)+SUMPRODUCT((LEFT(A1:A10,2)+0=26)+0)+...+SUMPRODUCT((LEFT(A1:A10,2)+0=96)+0
    Yet, I wish to find a simpler formula, and I might also need a 3rd digit check later for the research depending on the results of the 2nd one, and I'd need to list a hundred numbers with 3rd digit being 6 and another hundred for a any other number.
    The use of wildcards didn't work for me, but then again I'm a novice

    Please help

    P.S. These formulas only work if there no empty or non-numeric cells.
    P.P.S. There is a longer formula that also works for the LEFT function, if it's of any help:
    =SUMPRODUCT(0+(VALUE(LEFT(A1:A10+0))=5)) // for the count of cells that start with 5.

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,812
    Location
    Here is a UDF (User Defined Function) that will work. You must open the VBA editor, (right click on a sheet tab and select "View Code.")

    In the Editor make sure that the "Project Explorer" window is in View, (View Menu.)

    Right Click on the VBAProject(Your Workbook Name) and select Insert >> Module.

    Double click on that new module in the Project Explorer and paste this code in the right hand pane of the Editor.

    Option Explicit
    
    FunctionCountDigits(RangeToAnalyze As Range, _
                                   DigitPosition As Integer, _
                                   DigitToCount As Integer As Long
                         
    Dim Cel As Range
    Dim X As Long
    
    For Each Cel In RangeToAnalyze
         If Mid(CStr(Cel), DigitPosition, 1) = CStr(DigitToCount) Then
            X = X + 1
         End If
    Next Cel
    
    CountDigits = X
    End Function
    To use this Function in your worksheet, in a Cell, type the = sign, then click the fx button on the formula bar to Insert a Function. At the bottom of the "or Select a Category" dropdown list, select "User Defined Function" and select CountDigits. Then follow the Wizard instructions.

    You can also type in the Cell
    =CountDigits(
    Then Select the Range you want to use, then type a comma, the digit position to count, then a comma, the the digit to count, and a closing parenthesis.
    =CountDigits($A$1:$A$10,3,1)
    That example will count all the 1's in the third position. By using the Dolloar signs in the Range assignment, you can copy and paste the formula, and only have to change the position and digit in the new location.

    The Function is CountDigits(RaPiDly) or R for Range, P for Position, D for Digit. It should work for numbers up to 32,000 digits long
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Newbie
    Joined
    Aug 2013
    Posts
    4
    Location
    I have to admit, this is awesome I have never used VBA before, but your step by step guide is foolproof.
    Thank you so much, it works great!
    I wish I understood the code itself, but it's a different matter, VBA is on to-do list.
    Thanks again, SamT, you saved my dissertation.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    SUMPRODUCT will do it

    =SUMPRODUCT(--(MID(A1:A10,2,1)="6"))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Newbie
    Joined
    Aug 2013
    Posts
    4
    Location
    Quote Originally Posted by xld View Post
    SUMPRODUCT will do it

    =SUMPRODUCT(--(MID(A1:A10,2,1)="6"))
    Ha! I was wondering if there is a simple formula Thanks, xld!
    Quick question though, how can I replace this "6" (with the quotes) with a cell reference?
    If I've got 6 in the B6 cell for example,
    =SUMPRODUCT(--(MID(A1:A10,2,1)=B6)) won't work

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,812
    Location
    I have to admit, this is awesome I have never used VBA before, but your step by step guide is foolproof.
    Thank you so much, it works great!
    I wish I understood the code itself, but it's a different matter, VBA is on to-do list.
    Thanks again, SamT, you saved my dissertation.
    In VBA, Place the Keyboard cursor inside a word and press F1 for help on that word.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    VBAX Newbie
    Joined
    Aug 2013
    Posts
    4
    Location
    Quote Originally Posted by fleskj View Post
    Ha! I was wondering if there is a simple formula Thanks, xld!
    Quick question though, how can I replace this "6" (with the quotes) with a cell reference?
    If I've got 6 in the B6 cell for example,
    =SUMPRODUCT(--(MID(A1:A10,2,1)=B6)) won't work
    I've figured it out,
    it should be
    =SUMPRODUCT(--(MID(A1:A10,2,1)+0=B6)) for it to work

    EDIT: scratch that, it leaves me with the original problem of 1 digit cells, nevermind

    Quote Originally Posted by SamT View Post
    In VBA, Place the Keyboard cursor inside a word and press F1 for help on that word.
    Thanks! I'll look into that.
    Last edited by fleskj; 08-09-2013 at 09:55 AM.

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    It doesn't work for the same reason that your original formula doesn't work, you are comparing numeric items with strings. You need to string it

    =SUMPRODUCT(--(MID(A1:A10,2,1)=TEXT(B6,"General")))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    VBAX Newbie
    Joined
    Jan 2014
    Posts
    1
    Location
    As your A1 column is text then it can be traeted as such, if you use =SUMPRODUCT((MID(A1:A10&"000",2,1)+0=6)+0) then you will find it will work

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •