Can the "count if" function save me?

Posted by Dan on December 10, 2001 8:00 PM

Okay, my beginnermediate Excel knowledge is not helping me here. I've got a column of various numbers. Each cell contains multiple numbers separated by the || characters. For example:

1||2||4||8||9

You get the picture. Anyways, How do I count those individual numbers without having to insert a delimiter and separating out each number into its own column? I tried =COUNTIF(A1:A65536,1)to tally up all the 1's but that did not work. Big surprise.

HelllllllpMeee

Thanks in advance.

Posted by Super-T on December 10, 2001 8:22 PM

countif will not work.
Are all the numbers single digits?

Posted by Super-T on December 10, 2001 8:24 PM

Countif will not work.

Question - are all the numbers single digits?

Posted by Dan on December 10, 2001 8:33 PM

Re: Can the

Noo, unfortunately there are 1 and 2 digit numbers :-(

Posted by Juan Pablo G. on December 10, 2001 8:44 PM

Re: Can the

How about

=(LEN(A1) - LEN(SUBSTITUTE(A1,"||","")))/2 + 1

Juan Pablo G.

Posted by Dan on December 10, 2001 9:21 PM

Re: Can the - Getting close, but....

Juan, that formula works great for counting all the numbers in one cell. Can it be altered to count a specific number in a range of cells?

For example if I had the following one column of numbers:

1||2||3
2||3||4
3||4||5
1||2||3

and I wanted to count all the 3's in this one column, which formula could I use to do this? The outcome would be 4.

Thanks so much for the input! :)

Posted by Mike C on December 11, 2001 4:10 AM

use the following formula entered with Ctrl-Shift-Enter (for an array formula)

=COUNT(IF(NOT(ISERROR(FIND("||8||","||"&A1:A4&"||"))),1))

Where A1:A4 is the cell range you want to search and "||8||" contains the number you want to find (8 in this example)

This works for n-digit numbers.

Posted by Juan Pablo G. on December 11, 2001 5:38 AM

Re: Can the - Getting close, but....

If your numbers above are in A1:A4, put the number you want to search in B1 and this formula somewhere else.

=SUMPRODUCT(ISNUMBER(FIND(B1,$A$1:$A$4))+0)

Juan Pablo G.

Posted by Aladin Akyurek on December 11, 2001 7:39 AM

Mike --

The most important aspect of your formula is that it adds a welcome regularity to Dan's data. That of adding a "||" at the beginning and at the end of the search string and the target string. That's smart. That introduces the difference between a 1 and 11, 21, etc.

I'd like however to suggest the following array formula as alternative:

{=SUM((ISNUMBER(FIND("||"&1&"||","||"&A1:A7&"||"))+0))}

and its non-CSE sister:

=SUMPRODUCT((ISNUMBER(SEARCH("||"&1&"||","||"&A1:A7&"||"))+0))

The 1 as the first arg can be replaced by a cell ref that houses this number. Note that SEARCH or FIND both can be used in the context of Dan's problem.

Dan --

I had the impression that you used the whole column A as the target range. The array (or CSE) and SUMPRODUCT formulas, it doesn't matter, whichever you use will be too costly in performance! So be warned. Moreover, these 2 types of formulas don't accept A:A as a range arg. But, there is UDF (by Harlan Grove) which can be used to achieve just that.

Aladin

=========

Posted by Dan on December 11, 2001 9:30 AM

Re: Can the - Getting close, but....THANK YOU!

You're a lifesaver Juan, it works perfectly!!

Posted by Dan on December 11, 2001 9:35 AM

Thank you Mike and Aladin. Your input is extremely helpful, you have no idea how much this saves me!!