+ Reply to Thread
Results 1 to 12 of 12

Count individual digits

  1. #1
    James
    Guest

    Count individual digits

    Sorry about the blank post.
    I am trying to come up with a way to count individual digits i a list.
    I have 2 list in column b & c that go from row 1 - 38. I need to know how to
    count the total number of times each digit, 0,1,2,3,etc, appears in the list.
    Is there a function that will do this?

    Thank you,
    --
    James Bonds
    Excel Learner

  2. #2
    JulieD
    Guest

    Re: Count individual digits

    Hi James

    the COUNTIF function should give you this
    =COUNTIF(B1:C38,0)
    or if 0 is in cell E1
    =COUNTIF($B$1:$C$38,E1)
    and fill down for the other values

    --
    Cheers
    JulieD
    check out www.hcts.net.au/tipsandtricks.htm
    ....well i'm working on it anyway
    "James" <[email protected]> wrote in message
    news:[email protected]...
    > Sorry about the blank post.
    > I am trying to come up with a way to count individual digits i a list.
    > I have 2 list in column b & c that go from row 1 - 38. I need to know how
    > to
    > count the total number of times each digit, 0,1,2,3,etc, appears in the
    > list.
    > Is there a function that will do this?
    >
    > Thank you,
    > --
    > James Bonds
    > Excel Learner




  3. #3
    James
    Guest

    Re: Count individual digits

    I have tried COUNTIF and it did not work. Let me explain what I meant by
    individual digits. The numbers in the cells B1:C38 are either 3, 4, or 5
    digits, ie. 125, 8047, 19025. I need to know how many total times 0, 1, 2, 3,
    etc. show up within all of the cells.
    Sorry about not clarifying that in the begining.
    Thank you.
    --
    James Bonds
    Excel Learner


    "JulieD" wrote:

    > Hi James
    >
    > the COUNTIF function should give you this
    > =COUNTIF(B1:C38,0)
    > or if 0 is in cell E1
    > =COUNTIF($B$1:$C$38,E1)
    > and fill down for the other values
    >
    > --
    > Cheers
    > JulieD
    > check out www.hcts.net.au/tipsandtricks.htm
    > ....well i'm working on it anyway
    > "James" <[email protected]> wrote in message
    > news:[email protected]...
    > > Sorry about the blank post.
    > > I am trying to come up with a way to count individual digits i a list.
    > > I have 2 list in column b & c that go from row 1 - 38. I need to know how
    > > to
    > > count the total number of times each digit, 0,1,2,3,etc, appears in the
    > > list.
    > > Is there a function that will do this?
    > >
    > > Thank you,
    > > --
    > > James Bonds
    > > Excel Learner

    >
    >
    >


  4. #4
    JulieD
    Guest

    Re: Count individual digits

    Hi James

    try
    =SUMPRODUCT(--(LEN(B1:C38)-LEN(SUBSTITUTE(B1:C38,0,""))))

    where 0 is the number you're looking for.

    this will return 5 for the following
    ........B.............C
    1....100...........30
    2......5.............500

    --
    Cheers
    JulieD
    check out www.hcts.net.au/tipsandtricks.htm
    ....well i'm working on it anyway
    "James" <[email protected]> wrote in message
    news:[email protected]...
    >I have tried COUNTIF and it did not work. Let me explain what I meant by
    > individual digits. The numbers in the cells B1:C38 are either 3, 4, or 5
    > digits, ie. 125, 8047, 19025. I need to know how many total times 0, 1, 2,
    > 3,
    > etc. show up within all of the cells.
    > Sorry about not clarifying that in the begining.
    > Thank you.
    > --
    > James Bonds
    > Excel Learner
    >
    >
    > "JulieD" wrote:
    >
    >> Hi James
    >>
    >> the COUNTIF function should give you this
    >> =COUNTIF(B1:C38,0)
    >> or if 0 is in cell E1
    >> =COUNTIF($B$1:$C$38,E1)
    >> and fill down for the other values
    >>
    >> --
    >> Cheers
    >> JulieD
    >> check out www.hcts.net.au/tipsandtricks.htm
    >> ....well i'm working on it anyway
    >> "James" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Sorry about the blank post.
    >> > I am trying to come up with a way to count individual digits i a list.
    >> > I have 2 list in column b & c that go from row 1 - 38. I need to know
    >> > how
    >> > to
    >> > count the total number of times each digit, 0,1,2,3,etc, appears in the
    >> > list.
    >> > Is there a function that will do this?
    >> >
    >> > Thank you,
    >> > --
    >> > James Bonds
    >> > Excel Learner

    >>
    >>
    >>




  5. #5
    James
    Guest

    Re: Count individual digits

    That did work great!
    Thank you!
    --
    James Bonds
    Excel Learner


    "JulieD" wrote:

    > Hi James
    >
    > try
    > =SUMPRODUCT(--(LEN(B1:C38)-LEN(SUBSTITUTE(B1:C38,0,""))))
    >
    > where 0 is the number you're looking for.
    >
    > this will return 5 for the following
    > ........B.............C
    > 1....100...........30
    > 2......5.............500
    >
    > --
    > Cheers
    > JulieD
    > check out www.hcts.net.au/tipsandtricks.htm
    > ....well i'm working on it anyway
    > "James" <[email protected]> wrote in message
    > news:[email protected]...
    > >I have tried COUNTIF and it did not work. Let me explain what I meant by
    > > individual digits. The numbers in the cells B1:C38 are either 3, 4, or 5
    > > digits, ie. 125, 8047, 19025. I need to know how many total times 0, 1, 2,
    > > 3,
    > > etc. show up within all of the cells.
    > > Sorry about not clarifying that in the begining.
    > > Thank you.
    > > --
    > > James Bonds
    > > Excel Learner
    > >
    > >
    > > "JulieD" wrote:
    > >
    > >> Hi James
    > >>
    > >> the COUNTIF function should give you this
    > >> =COUNTIF(B1:C38,0)
    > >> or if 0 is in cell E1
    > >> =COUNTIF($B$1:$C$38,E1)
    > >> and fill down for the other values
    > >>
    > >> --
    > >> Cheers
    > >> JulieD
    > >> check out www.hcts.net.au/tipsandtricks.htm
    > >> ....well i'm working on it anyway
    > >> "James" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > Sorry about the blank post.
    > >> > I am trying to come up with a way to count individual digits i a list.
    > >> > I have 2 list in column b & c that go from row 1 - 38. I need to know
    > >> > how
    > >> > to
    > >> > count the total number of times each digit, 0,1,2,3,etc, appears in the
    > >> > list.
    > >> > Is there a function that will do this?
    > >> >
    > >> > Thank you,
    > >> > --
    > >> > James Bonds
    > >> > Excel Learner
    > >>
    > >>
    > >>

    >
    >
    >


  6. #6
    Bob Phillips
    Guest

    Re: Count individual digits

    If each digits appears only once per cell then

    =SUMPRODUCT(--(ISNUMBER(FIND(2,A1:A100))))

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "James" <[email protected]> wrote in message
    news:[email protected]...
    > I have tried COUNTIF and it did not work. Let me explain what I meant by
    > individual digits. The numbers in the cells B1:C38 are either 3, 4, or 5
    > digits, ie. 125, 8047, 19025. I need to know how many total times 0, 1, 2,

    3,
    > etc. show up within all of the cells.
    > Sorry about not clarifying that in the begining.
    > Thank you.
    > --
    > James Bonds
    > Excel Learner
    >
    >
    > "JulieD" wrote:
    >
    > > Hi James
    > >
    > > the COUNTIF function should give you this
    > > =COUNTIF(B1:C38,0)
    > > or if 0 is in cell E1
    > > =COUNTIF($B$1:$C$38,E1)
    > > and fill down for the other values
    > >
    > > --
    > > Cheers
    > > JulieD
    > > check out www.hcts.net.au/tipsandtricks.htm
    > > ....well i'm working on it anyway
    > > "James" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Sorry about the blank post.
    > > > I am trying to come up with a way to count individual digits i a list.
    > > > I have 2 list in column b & c that go from row 1 - 38. I need to know

    how
    > > > to
    > > > count the total number of times each digit, 0,1,2,3,etc, appears in

    the
    > > > list.
    > > > Is there a function that will do this?
    > > >
    > > > Thank you,
    > > > --
    > > > James Bonds
    > > > Excel Learner

    > >
    > >
    > >




  7. #7
    Bob Phillips
    Guest

    Re: Count individual digits

    forgot to ad that if there could be more than one instance of a number in a
    cell, use

    =SUMPRODUCT(--(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,2,""))))

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "James" <[email protected]> wrote in message
    news:[email protected]...
    > I have tried COUNTIF and it did not work. Let me explain what I meant by
    > individual digits. The numbers in the cells B1:C38 are either 3, 4, or 5
    > digits, ie. 125, 8047, 19025. I need to know how many total times 0, 1, 2,

    3,
    > etc. show up within all of the cells.
    > Sorry about not clarifying that in the begining.
    > Thank you.
    > --
    > James Bonds
    > Excel Learner
    >
    >
    > "JulieD" wrote:
    >
    > > Hi James
    > >
    > > the COUNTIF function should give you this
    > > =COUNTIF(B1:C38,0)
    > > or if 0 is in cell E1
    > > =COUNTIF($B$1:$C$38,E1)
    > > and fill down for the other values
    > >
    > > --
    > > Cheers
    > > JulieD
    > > check out www.hcts.net.au/tipsandtricks.htm
    > > ....well i'm working on it anyway
    > > "James" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Sorry about the blank post.
    > > > I am trying to come up with a way to count individual digits i a list.
    > > > I have 2 list in column b & c that go from row 1 - 38. I need to know

    how
    > > > to
    > > > count the total number of times each digit, 0,1,2,3,etc, appears in

    the
    > > > list.
    > > > Is there a function that will do this?
    > > >
    > > > Thank you,
    > > > --
    > > > James Bonds
    > > > Excel Learner

    > >
    > >
    > >




  8. #8
    JulieD
    Guest

    Re: Count individual digits

    you're welcome

    --
    Cheers
    JulieD
    check out www.hcts.net.au/tipsandtricks.htm
    ....well i'm working on it anyway
    "James" <[email protected]> wrote in message
    news:[email protected]...
    > That did work great!
    > Thank you!
    > --
    > James Bonds
    > Excel Learner
    >
    >
    > "JulieD" wrote:
    >
    >> Hi James
    >>
    >> try
    >> =SUMPRODUCT(--(LEN(B1:C38)-LEN(SUBSTITUTE(B1:C38,0,""))))
    >>
    >> where 0 is the number you're looking for.
    >>
    >> this will return 5 for the following
    >> ........B.............C
    >> 1....100...........30
    >> 2......5.............500
    >>
    >> --
    >> Cheers
    >> JulieD
    >> check out www.hcts.net.au/tipsandtricks.htm
    >> ....well i'm working on it anyway
    >> "James" <[email protected]> wrote in message
    >> news:[email protected]...
    >> >I have tried COUNTIF and it did not work. Let me explain what I meant by
    >> > individual digits. The numbers in the cells B1:C38 are either 3, 4, or
    >> > 5
    >> > digits, ie. 125, 8047, 19025. I need to know how many total times 0, 1,
    >> > 2,
    >> > 3,
    >> > etc. show up within all of the cells.
    >> > Sorry about not clarifying that in the begining.
    >> > Thank you.
    >> > --
    >> > James Bonds
    >> > Excel Learner
    >> >
    >> >
    >> > "JulieD" wrote:
    >> >
    >> >> Hi James
    >> >>
    >> >> the COUNTIF function should give you this
    >> >> =COUNTIF(B1:C38,0)
    >> >> or if 0 is in cell E1
    >> >> =COUNTIF($B$1:$C$38,E1)
    >> >> and fill down for the other values
    >> >>
    >> >> --
    >> >> Cheers
    >> >> JulieD
    >> >> check out www.hcts.net.au/tipsandtricks.htm
    >> >> ....well i'm working on it anyway
    >> >> "James" <[email protected]> wrote in message
    >> >> news:[email protected]...
    >> >> > Sorry about the blank post.
    >> >> > I am trying to come up with a way to count individual digits i a
    >> >> > list.
    >> >> > I have 2 list in column b & c that go from row 1 - 38. I need to
    >> >> > know
    >> >> > how
    >> >> > to
    >> >> > count the total number of times each digit, 0,1,2,3,etc, appears in
    >> >> > the
    >> >> > list.
    >> >> > Is there a function that will do this?
    >> >> >
    >> >> > Thank you,
    >> >> > --
    >> >> > James Bonds
    >> >> > Excel Learner
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>




  9. #9
    Bill Martin -- (Remove NOSPAM from address)
    Guest

    Re: Count individual digits

    JulieD wrote:
    > Hi James
    >
    > try
    > =SUMPRODUCT(--(LEN(B1:C38)-LEN(SUBSTITUTE(B1:C38,0,""))))
    >
    > where 0 is the number you're looking for.
    >
    > this will return 5 for the following
    > .......B.............C
    > 1....100...........30
    > 2......5.............500
    >


    =============

    What's the "--" do Julie? I tried to look it up in the Excel Help
    system but it won't fess up that such a function exists. Nor do I
    stumble across anything about it in the "Excel 97 Bible" as an operator
    or as a function. And it's not described by Excel under the SUMPRODUCT
    function.

    Bill

  10. #10
    JulieD
    Guest

    Re: Count individual digits

    Hi Bill

    for details on the SUMPRODUCT function itself check out
    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

    and here's a link to a newsgroup post by Bob Phillips where he explains the
    double unary (--) and quotes a post by Harlan Grove on the subject
    http://tinyurl.com/bv42x

    (good luck!)
    --
    Cheers
    JulieD
    check out www.hcts.net.au/tipsandtricks.htm
    ....well i'm working on it anyway
    "Bill Martin -- (Remove NOSPAM from address)" <[email protected]>
    wrote in message news:[email protected]...
    > JulieD wrote:
    >> Hi James
    >>
    >> try
    >> =SUMPRODUCT(--(LEN(B1:C38)-LEN(SUBSTITUTE(B1:C38,0,""))))
    >>
    >> where 0 is the number you're looking for.
    >>
    >> this will return 5 for the following
    >> .......B.............C
    >> 1....100...........30
    >> 2......5.............500
    >>

    >
    > =============
    >
    > What's the "--" do Julie? I tried to look it up in the Excel Help system
    > but it won't fess up that such a function exists. Nor do I stumble across
    > anything about it in the "Excel 97 Bible" as an operator or as a function.
    > And it's not described by Excel under the SUMPRODUCT function.
    >
    > Bill




  11. #11
    Bob Phillips
    Guest

    Re: Count individual digits


    "JulieD" <[email protected]> wrote in message
    news:[email protected]...
    > Hi Bill
    >
    > for details on the SUMPRODUCT function itself check out
    > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
    >
    > and here's a link to a newsgroup post by Bob Phillips where he explains

    the
    > double unary (--) and quotes a post by Harlan Grove on the subject
    > http://tinyurl.com/bv42x


    But I used it, I didn't sue it :-).



  12. #12
    Bill Martin -- (Remove NOSPAM from address)
    Guest

    Re: Count individual digits

    Bob Phillips wrote:
    > "JulieD" <[email protected]> wrote in message
    > news:[email protected]...
    >
    >>Hi Bill
    >>
    >>for details on the SUMPRODUCT function itself check out
    >>http://www.xldynamic.com/source/xld.SUMPRODUCT.html
    >>
    >>and here's a link to a newsgroup post by Bob Phillips where he explains

    >
    > the
    >
    >>double unary (--) and quotes a post by Harlan Grove on the subject
    >>http://tinyurl.com/bv42x

    >
    >
    > But I used it, I didn't sue it :-).
    >
    >

    =============

    Thanks to Julie and Bob both for the education. Somehow I've never
    stumbled across that usage before.

    Bill

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1