Frequency
Does each row contain unique values? If so, try the following...
1) Let H1 and I1 contain 5 and 10
2) Let H2 and I2 contain 1 and 10
3) Then enter the following formula in J2, and copy down:
=SUM(--(MMULT(ISNUMBER(MATCH($A$1:$F$3,H1:I1,0))+0,TRANSP OSE(COLUMN($A$1:
$F$3)^0))=2))
Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.
Hope this helps!
In article ,
Will wrote:
Peo,
thanks for the solution.. Wat I mean is, is there a formula tat Match a few
group of numbers to find out which 2 numbers appear the most freq.
example
1st group of 6 numbers: 2 (A1), 1 (B1), 7 (C1), 10 (D1), 8 (E1), 5 (F1)
2nd group of 6 numbers: 5 (A2), 10 (B2), 11 (C2), 12 (D2), 20 (E2), 1 (F2),
3rd group of 6numbers: 15 (A3), 10 (B3), 16 (C3), 17 (D3), 18 (E3), 1 (F3),
Number citeria (5 & 10) appear: 2
Number citeria (1 & 10) appear: 3
countif enable me to match a Single range with a Single citeria but now i
have multiple Range to match with 2 citeria.
|