# Can i count highlighted numbers within a column

1. ## 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.

2. ## 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?

3. ## 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. ## 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. ## 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)))

6. ## Re: Can i count highlighted numbers within a column

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

7. ## 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. ## Re: Can i count highlighted numbers within a column

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

9. ## Re: Can i count highlighted numbers within a column

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

10. ## Re: Can i count highlighted numbers within a column

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. ## 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. ## 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. ## 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???

14. ## 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)

15. ## Re: Can i count highlighted numbers within a column

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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