Counting Occurance of Particular Digit
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..
Last edited by newbie1234; 12-13-2008 at 07:11 AM. Reason: Solved
=sumproduct(len(a1:a4) - len(substitute(a1:a4, "0", "")))
Entia non sunt multiplicanda sine necessitate
Thanks alot..Originally Posted by shg
perhaps something like this
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..
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, thenwill return True if there is an F in the cell. Is that what you want?
Please Login or Register to view this content.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Sorry it was a typo error..Just realised it now..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, thenwill return True if there is an F in the cell. Is that what you want?Please Login or Register to view this content.
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..
Last edited by newbie1234; 12-12-2008 at 10:33 PM.
How about this:
Does that help?Please Login or Register to view this content.
Hi there,How about this:
Does that help?Please Login or Register to view this content.
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
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
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
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..
The wildcards are necessary:
Although, I'm a bit puzzled. B2's contents are derived from A2's contents.Please Login or Register to view this content.
So, wouldn't there always be a match?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks