Countif Question

Posted by James on June 26, 2001 3:30 PM

I have a worksheet that has numbers like this "303,302,508,408" there are several rows with info like this and I want to count each occurence of a number. Like how many times does 303 show up. I do not want to put each number in a colum by itself. Is there anyway to do this?

Thanks.

Posted by Kevin James on June 26, 2001 3:45 PM

James, are you asking how the syntax for countif works?

At the end of your data block (row or column):
=countif(range,criteria) where range is your block of data and criteria is the number you are looking for.
So that you don't have to edit the formula, you can point "criteria" to a cell that allows you to enter whatever number you are looking for.

Kevin

Posted by James on June 27, 2001 9:40 AM

No. I know how it works. But it does not work with more then one number per cell. I have the numbers seperated with "," and want to count them without putting them in seperate colums. I can write a macro but want to know if there is a easer way?

In the cell there is a set of numbers like this:
302,104,451,096
I want to count over severl cells how many of each number there is. But I do not want to put them in there own colums?

Thanks
James

Posted by Aladin Akyurek on June 27, 2001 11:00 AM

James,

OK. I'll assume that a "partial number" doesn't have a repeat in the same cell like in "303,304,303". What I'm going to propose will count such cases once. For the preceeding example, you'll have a count of 1 for 303 and a count of 1 for 304. If this is acceptable to you, read farther.

I'll assume all your entries to be in A from A2 on.

Put all the numbers you'd want to count in B from B2 on.

In C2 array-enter: =SUM(ISNUMBER(SEARCH(B$1&",",$A$2:$A$100))+0)+SUM(ISNUMBER(SEARCH(","&B$1,$A$2:$A$100))) [ copy down this as far as needed ]

Note1. Hit CONTROL+SHIFT+ENTER at the same time (not just ENTER) to enter this array formula.

Note2. The formatting of the entries should not matter as long as theere a commas to separate the "partial numbers".

Aladin

Posted by Aladin Akyurek on June 27, 2001 12:31 PM

Typo & ...

The copy of the array formula I pasted is not the intended one. Here is the correct version:

=SUM(ISNUMBER(SEARCH(B1&",",$A$2:$A$100))+0)+SUM(ISNUMBER(SEARCH(","&B1,$A$2:$A$100))+0)

Sorry about that. BTW, this will count repeats also that occur in the same entry/cell.

Aladin

===========

Posted by James on June 27, 2001 2:58 PM

Re: Typo & ...

It looks good but there can be several repeats in one cell. This seems to only count two. Is there anything else.

Thanks for the Help

James

The copy of the array formula I pasted is not the intended one. Here is the correct version:

Posted by Aladin Akyurek on June 29, 2001 11:45 AM

Repeats...

James,

The following array-formula assumes cells with 3 numbers (separated by commas). If you have more numbers (hope not too many), it can be extended to cover them too.

=SUM((ISNUMBER(SEARCH(B1,LEFT($A$2:$A$4,LEN(B1))))+0))+SUM((ISNUMBER(SEARCH(B1,MID($A$2:$A$4,LEN(B1)+2,LEN(B1))))+0))+SUM((ISNUMBER(SEARCH(B1,RIGHT($A$2:$A$4,LEN(B1))))+0))

I still expect a column (e.g., B) with distinct numbers for which you want a count. The formula can be array-entered in C1 and copied down.

Aladin

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