+ Reply to Thread
Results 1 to 15 of 15

Can i count highlighted numbers within a column

  1. #1
    Forum Contributor
    Join Date
    11-13-2010
    Location
    Warren, Michigan
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    307

    Can i count highlighted numbers within a column

    Is there a formula to "count" a column of all highlighted numbers?

    Or is there a formula to "count" all "double" numbers within that particular column?

    See attached - I highlighted all occurrences of "double" numbers (total is 8).

    I used conditional formating to highlight those numbers in the original worksheet.
    Attached Files Attached Files
    Last edited by khank; 01-08-2011 at 12:33 PM.

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Can i count highlighted numbers within a column

    I *think* this formula returns the count of cells that contain duplicate digits:

    Please Login or Register  to view this content.
    (Note: I tweaked the formula slightly to correct for when all values contain duplicate digits.)
    Does that work?
    Last edited by Ron Coderre; 01-08-2011 at 11:11 AM.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Can i count highlighted numbers within a column

    Perhaps:

    Please Login or Register  to view this content.
    for "double" only (and not triple say) change >1 to =2

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Can i count highlighted numbers within a column

    I like this better.
    Somehow I thought our number system had more than 10 options (0-9) and took the longer approach.

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Can i count highlighted numbers within a column

    try
    =SUMPRODUCT((LEFT(A1:A30)=MID(A1:A30,2,1))+(RIGHT(A1:A30)=MID(A1:A30,2,1))+(LEFT(A1:A30)=RIGHT(A1:A30)))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  6. #6
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Can i count highlighted numbers within a column

    In that formula, triples are triple counted
    example: 111 counts as 3

  7. #7
    Forum Contributor
    Join Date
    11-13-2010
    Location
    Warren, Michigan
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    307

    Re: Can i count highlighted numbers within a column

    Yes it works thanks.

    Is the same code adaptable to count to just two (2) even numbers within a cell?

    Example on Forum Sample 8: 342 there are 2 even numbers (2 and 4) or 083, etc.???

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Can i count highlighted numbers within a column

    @ron yep i know! i realised that when i tested it

  9. #9
    Forum Contributor
    Join Date
    11-13-2010
    Location
    Warren, Michigan
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    307

    Re: Can i count highlighted numbers within a column

    Triples don't bother me, the chances of triple numbers are extremely slim.

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Can i count highlighted numbers within a column

    Quote Originally Posted by khank
    Is the same code adaptable to count to just two (2) even numbers within a cell?
    Do you mean:

    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    11-13-2010
    Location
    Warren, Michigan
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    307

    Re: Can i count highlighted numbers within a column

    I've tried the new coding

    =SUMPRODUCT(((MOD(MID(A1:A30,1,1),2)+MOD(MID(A1:A30,2,1),2)+MOD(MID(A1:A30,3,1),2))<2)+0)

    I'm getting "#VALUE!" popping up? (cells within the column are formatted as "general").

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Can i count highlighted numbers within a column

    In your sample the numbers are stored as text (with leading zero) and the formula does not generate #VALUE!

    Try:

    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    11-13-2010
    Location
    Warren, Michigan
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    307

    Counting "EVEN" numbers in a column

    See attached sample.

    I tried both codes and neither seem to be working?

    As you'll see in Columns A, B, C they are formatted as "Text", and yes I get a number in Columns E, F, G but the number is not correct.

    I did a manual count at the bottom of A, B, C. (I colored the particular cells only as a "visual" thing.)

    In Columns I, J, K are the same numbers formatted as "General", and I get a #VALUE! in Columns M, N, O - I'm assuming that is because those numbers where originally "Text" formatted???
    Attached Files Attached Files

  14. #14
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Can i count highlighted numbers within a column

    In all cases the "numbers are stored as text" - the fact that General format has been applied to I:K has no effect on the underlying values.

    The issues you have re: calculation relate solely to blanks within the precedent ranges (previously unknown condition).

    You get #VALUE! errors in the 2nd examples because of the lack of TEXT(range,"000") which forces the blanks to become Null (in effect) - without it the MOD of the MIDs will fail on the blanks (#VALUE!)

    For the same reason you get "over-count" in the version WITH the TEXT as the blanks are seen as 000 and per your requirements 0 is included as valid - hence all the blanks are added to the final count.

    Given all of the above, you can use:

    Please Login or Register  to view this content.
    Note however that your manually calculated value for column B is incorrect and the result should be 6 rather than 4 (B5,B8,B13,B17,B19:B20)
    Last edited by DonkeyOte; 01-08-2011 at 06:31 PM. Reason: Typo Null not 000 (2nd part!)

  15. #15
    Forum Contributor
    Join Date
    11-13-2010
    Location
    Warren, Michigan
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    307

    Re: Can i count highlighted numbers within a column

    Thank everyone for all your help, I got things to work.

+ 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