Counting Occurance of Particular Digit

**newbie1234** · 12-12-2008, 02:39 PM

Hi everyone...

I have a slight problem...I have a range of values..

0113
1240
8430
0903

I need to count the occurance of a a particular no. from 0-9.. So i want to know how many times say 0 appears in that range of values etc so on till 9. I tried using CountIF but the problem i face is lets say in my cell 0903 there are 2 0s inside, it doesnt count this 2 zero.. I do not know why this is a problem..

Anyone care to share some light on this issue..

**shg** · 12-12-2008, 02:46 PM

=sumproduct(len(a1:a4) - len(substitute(a1:a4, "0", "")))

**newbie1234** · 12-12-2008, 02:55 PM

Originally Posted by shg

=sumproduct(len(a1:a4) - len(substitute(a1:a4, "0", "")))

Thanks alot..

**martindwilson** · 12-12-2008, 02:55 PM

perhaps something like this

**newbie1234** · 12-12-2008, 03:00 PM

Just to add on with another problem also to deal with occurance..

I have this code B744DFCC on Cell A1..

My excel has a criteria which i need to search for "7". So if i find F in my code, i display "Y" else i display X. How do i do it?

Thanks..

**ChemistB** · 12-12-2008, 05:34 PM

I'm not sure what your search for "7" has to do with your search for "F"? Do you want to only look for F's if you find a 7? If you are just looking to see if a string has an F in it, then

Please Login or Register  to view this content.

will return True if there is an F in the cell. Is that what you want?

**newbie1234** · 12-12-2008, 10:30 PM

Originally Posted by ChemistB

I'm not sure what your search for "7" has to do with your search for "F"? Do you want to only look for F's if you find a 7? If you are just looking to see if a string has an F in it, then

Please Login or Register  to view this content.

will return True if there is an F in the cell. Is that what you want?

Sorry it was a typo error..Just realised it now..

Hi there, your forumla works for me, but its returning me with a True or False answer. How can i customise the msg to be Y or X..

**Ron Coderre** · 12-12-2008, 10:52 PM

How about this:

Please Login or Register  to view this content.

Does that help?

**newbie1234** · 12-13-2008, 12:35 AM

Originally Posted by Ron Coderre

How about this:

Please Login or Register  to view this content.

Does that help?

Hi there,

I've tried using this method in my excel sheet. I've attached the excel im currently doing.. By right your If Statement should be working but i do not know why...Anyone care to have a look at it..

My F2 cell by right suppose to display 8 since it matches the criteria i have set, but why is it displaying and X. Have i gone anywhere wrong in my formula..?

Thanks

**shg** · 12-13-2008, 01:31 AM

The application of Ron's formula to your usage would be =IF(COUNTIF(B2, "*" & MID(A2,1,2) & "*" ), F1, "X"), which returns 8.

**newbie1234** · 12-13-2008, 01:33 AM

Originally Posted by shg

The application of Ron's formula to your usage would be =IF(COUNTIF(B2, "*" & MID(A2,1,2) & "*" ), F1, "X"), which returns 8.

Thanks alot Mod.

I tried this "* MID(A2,1,2) *" didnt work...Thanks alot for pointing out..:P

**newbie1234** · 12-13-2008, 01:39 AM

Originally Posted by shg

The application of Ron's formula to your usage would be =IF(COUNTIF(B2, "*" & MID(A2,1,2) & "*" ), F1, "X"), which returns 8.

Mod I have a question...

Lets say I have 2 Criterias - "*" & MID(A2,1,2) & "*" and "*" & MID(A2,3,2) & "*"

How do i put these 2 in my formula to check for 2 criteria at the same time?

Thanks

**martindwilson** · 12-13-2008, 06:47 AM

whats wrong with find? i criteria
=IF(ISERROR(FIND(B2,$A$2)),"X","Y")
2 criteria true if both there
=IF(ISERROR(AND(FIND(B12,$A$12),FIND(C12,$A$12))),"X","Y")
i cant help wondering why you are extracting a string from A2 then testing to see if it is found in A2 which it always must be!!!!

**newbie1234** · 12-13-2008, 07:10 AM

Originally Posted by martindwilson

whats wrong with find? i criteria
=IF(ISERROR(FIND(B2,$A$2)),"X","Y")
2 criteria true if both there
=IF(ISERROR(AND(FIND(B12,$A$12),FIND(C12,$A$12))),"X","Y")
i cant help wondering why you are extracting a string from A2 then testing to see if it is found in A2 which it always must be!!!!

Thanks..Problem solved..

Can Mods close this thread..

**Ron Coderre** · 12-13-2008, 09:21 AM

The wildcards are necessary:

Please Login or Register  to view this content.

Although, I'm a bit puzzled. B2's contents are derived from A2's contents.
So, wouldn't there always be a match?

Counting Occurance of Particular Digit

LinkBack

Thread Tools

Rate This Thread

Display

Counting Occurance of Particular Digit

If cell contains "F", return "Y"..otherwise "X"

Find one string in an other

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions