Le 24/04/01 13:01, « jimljrx » <[log in to unmask]> a écrit :
>
> That's brilliant. The function should be useful to many people since it
> works with text as well as numbers.
>
hi Jim,
I was surprised myself of the fact it works with both (automatic type
conversion here).
>
> assume that the code values can occur not only once in the multicode
> sequence.
> how could one adapt your function to gove a count of 2
> for multicode 33
> (assuming that 3 is a basic code value)
>
hello Erich,
how are you?
Ok, ok, if you ask for it, you'll get it... But, I don't think it is a good
practice to duplicate codes in an answer (unless you give me a good
example). Anyway here goes (it's a bit more complicated, and now you have to
bother with the type of what's in the code list)
=SUM(IF(FIXED($A11,0)=MID($D$1:$D$10,COLUMN(OFFSET($A$1,0,0,1,MAX(LEN($D$1:$
D$10)))),1),1,0))
(I told you!)
Array enter, of course.
The above version is if the A11:A20 cell contents are numeric. You don't
need the type conversion if you enter them as text ('1 and so on)
=SUM(IF($A11=MID($D$1:$D$10,COLUMN(OFFSET($A$1,0,0,1,MAX(LEN($D$1:$D$10)))),
1),1,0))
François
|