Frequency

[Guest]

Hi,

I have a project to count how freq a pair of numbers appear amount A FEW
groups of 6 numbers.

Lets say I have 2 pairs of 6 numbers, (1,2,3,4,5,6) & (1,3,5,7,9) and pairs
(1,3) & (3,5) & (1,5) appears twice in these 2 pairs of 6 numbers. I am
unable to
find a suitable formula for this problem and the nearest I can do is COUNTIF
but COUNTIF can only cater 1 CRITERIA but in these i believe I have 2 or
more.

 

[Peo Sjoblom]

I am not sure I understand you? Do you mean like this

=SUM(COUNTIF(Range1,{1;3}))

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[Guest]

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.

 

[Guest]

Can someone pls help!

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.

 

[Domenic]

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,TRANSPOSE(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!