Counting

  • Thread starter Thread starter Dave R.
  • Start date Start date
D

Dave R.

Data is like (one name per cell)


Mike Tim Bob Sally (key)

many rows of this;

Mike Sally Ray Jake
Jim Billy Sam Luke
Bob Sally Tim Mike
Mike Bob Sally Rudolph


What I'm trying to do is count/signal anytime that all 4 names from the key
set are in, even if they are not in the same order. E.g. if the key is "1,2"
it should count rows that contain "1,2" and "2,1"

I tried some array match formulas and such, can't get it to work. It should
count 1 for the above sample set.
 
That works, dunno why I didn't think of that!


Mark Graesser said:
Hi Dave,
You could use the following formula in a helper column and then sum that column:
=IF(AND(COUNTIF(A3:D3,$A$1)=1,COUNTIF(A3:D3,$B$1)=1,COUNTIF(A3:D3,$C$1)=1,CO
UNTIF(A3:D3,$D$1)=1),1,0)

A1 thru D1 is the test set, A3:D3 is the first set to be tested.

Good Luck,
Mark Graesser
(e-mail address removed)
Boston MA

----- Dave R. wrote: -----

Data is like (one name per cell)


Mike Tim Bob Sally (key)

many rows of this;

Mike Sally Ray Jake
Jim Billy Sam Luke
Bob Sally Tim Mike
Mike Bob Sally Rudolph


What I'm trying to do is count/signal anytime that all 4 names from the key
set are in, even if they are not in the same order. E.g. if the key is "1,2"
it should count rows that contain "1,2" and "2,1"

I tried some array match formulas and such, can't get it to work. It should
count 1 for the above sample set.
Click to expand...
 
On 5/12/04 3:45 PM, in article (e-mail address removed), "Dave
Hi Dave,

Try,

=SUM(IF((ISNUMBER(FIND("Mike",A1:A4)))*(ISNUMBER(FIND("Tim",A1:A4)))*(ISNUMB
ER(FIND("Bob",A1:A4)))*(ISNUMBER(FIND("Sally",A1:A4))),1,0))

to be entered using CTRL+SHIFT+ENTER
 
Back
Top