COUNTIF
stefi,
thanks, wat i mean is I have 20-30 groups of 6 and I need to find out how
often a certain pair appear..
Meaning 1st - 30th group of 6 numbers, pair 1 & 5 appear 20 times...
somethin like tat
"Stefi" wrote:
You do not have to enter the formula 20-30 times, you have to fill down the
formula through 20-30 rows!
Regards,
Stefi
€˛Will€¯ ezt Ć*rta:
Stefi,
problem solved.. if i have 20-30 groups of 6, meaning i have to do tis
formula 20-30 times?
"Stefi" wrote:
This is probably not the optimal sulution but for the moment I couldn't find
the single formula (I guess it should be an array formula) fulfilling your
request. Say your data are arranged in this pattern:
groups of 6 pairs result
A1 ... F1 H1 I1 J1
A2 ... F2 J2
J3
Enter in J1
=IF(AND(COUNTIF($A1:$F1,H$1)=1,COUNTIF($A1:$F1,I$1 )=1),1,0)
Fill it down to the last group of 6 (2 in my example)
Autosum column J in J3
Regards,
Stefi
€˛Will€¯ ezt Ć*rta:
Stefi,
my numbers are arranged in different cells in a row.. A1(1) B1(2) C1(3) etc..
My pairs of numbers are arranged also in different cells.
"Stefi" wrote:
How are your numbers arranged? Are they comma separated strings in one cell
as in your example, or are they stored in separate cells, e.g. in range A1:A6
like that:
A1 1
A2 2
etc.
Also, how are arranged the pairs of numbers?
Regards,
Stefi
€˛Will€¯ ezt Ć*rta:
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) 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.
|