Hello. Thanks for reading my question. My problem is directly related to
Excel 2003's column limitation of 256. Situation: I have the following array
formula in a cell, which counts the number of numerical entries that occur
three times in the range from A1:IV1 ....
=SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)=3,1,0))/3. The formula is replicated
downward for 1,000 rows. This works fine. I have several sets of these one
thousand blocks of numbers (separated by blank rows), and therein comes my
pain. The second block of cells begins at A2000. I now need to count two rows
to see how many "three occurrences" there are in ( A1:IV1,
A2000:IV2000........B2:IV2,A2001:IV2001......) . In other words, I want the
formula to 'see' these two ranges as one, if possible. I cannot simply apply
the formula to the second block of cell rows because, as an example, a number
might occur "two times" in A1:IV1 and "one time" in A2000:IV2000 and would
not get counted in the sum. Thank you.

Have you tried it like this? :

=SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)+COUNTIF($A2000: $IV2000,$A2000:$IV2000)=3,1,0))/
3

Hope this helps.

Pete

On Dec 21, 7:55*am, Roger H. wrote:
Hello. Thanks for reading my question. My problem is directly related to
Excel 2003's column limitation of 256. Situation: I have the following array
formula in a cell, which counts *the number of numerical entries that *occur
three times in the range from A1:IV1 .... *
=SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)=3,1,0))/3. The formula is replicated
downward for 1,000 rows. This works fine. I have several sets of these one
thousand blocks of numbers (separated by blank rows), and therein comes my
pain. The second block of cells begins at A2000. I now need to count two rows
to see how many "three occurrences" there are in ( A1:IV1,
A2000:IV2000........B2:IV2,A2001:IV2001......) . In other words, I want the
formula to 'see' these two ranges as one, if possible. I cannot simply apply
the formula to the second block of cell rows because, as an example, a number
might occur "two times" in A1:IV1 and "one time" in A2000:IV2000 and would
not get counted in the sum. Thank you.

Yes. I had tried that particular approach, Pete. But it did not count all the
valid occurrences in rows where there were only "one" count of a number
occuring three times. I have all the rows conditionally formatted so that
they will be easy to spot --- ( Formula is ) : =COUNTIF($A1:$IV1,A1)=3. Maybe
I'll have to settle for an approximation with what formulas I have. Thank you.


"Pete_UK" wrote:

Have you tried it like this? :

=SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)+COUNTIF($A2000: $IV2000,$A2000:$IV2000)=3,1,0))/
3

Hope this helps.

Pete

On Dec 21, 7:55 am, Roger H. wrote:
Hello. Thanks for reading my question. My problem is directly related to
Excel 2003's column limitation of 256. Situation: I have the following array
formula in a cell, which counts the number of numerical entries that occur
three times in the range from A1:IV1 ....
=SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)=3,1,0))/3. The formula is replicated
downward for 1,000 rows. This works fine. I have several sets of these one
thousand blocks of numbers (separated by blank rows), and therein comes my
pain. The second block of cells begins at A2000. I now need to count two rows
to see how many "three occurrences" there are in ( A1:IV1,
A2000:IV2000........B2:IV2,A2001:IV2001......) . In other words, I want the
formula to 'see' these two ranges as one, if possible. I cannot simply apply
the formula to the second block of cell rows because, as an example, a number
might occur "two times" in A1:IV1 and "one time" in A2000:IV2000 and would
not get counted in the sum. Thank you.

You're welcome, Roger.

Pete

On Dec 21, 11:22*am, Roger H.
wrote:
Yes. I had tried that particular approach, Pete. But it did not count all the
valid occurrences in rows where there were only "one" count of a number
occuring three times. I have all the rows conditionally formatted so that
they will be easy to spot --- ( Formula is ) : =COUNTIF($A1:$IV1,A1)=3.. Maybe
I'll have to settle for an approximation with what formulas I have. Thank you.



"Pete_UK" wrote:
Have you tried it like this? :

=SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)+COUNTIF($A2000: $IV2000,$A2000:$IV2000)=3*,1,0))/
3

Hope this helps.

Pete

On Dec 21, 7:55 am, Roger H. wrote:
Hello. Thanks for reading my question. My problem is directly related to
Excel 2003's column limitation of 256. Situation: I have the following array
formula in a cell, which counts *the number of numerical entries that *occur
three times in the range from A1:IV1 .... *
=SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)=3,1,0))/3. The formula is replicated
downward for 1,000 rows. This works fine. I have several sets of these one
thousand blocks of numbers (separated by blank rows), and therein comes my
pain. The second block of cells begins at A2000. I now need to count two rows
to see how many "three occurrences" there are in ( A1:IV1,
A2000:IV2000........B2:IV2,A2001:IV2001......) . In other words, I want the
formula to 'see' these two ranges as one, if possible. I cannot simply apply
the formula to the second block of cell rows because, as an example, a number
might occur "two times" in A1:IV1 and "one time" in A2000:IV2000 and would
not get counted in the sum. Thank you.- Hide quoted text -

- Show quoted text -

Not sure I understand what you want but somehting like this might work:

=SUMPRODUCT(--(FREQUENCY((A1:G1,A5:G5),(A1:G1,A5:G5))=3))

--
Biff
Microsoft Excel MVP


"Pete_UK" wrote in message
...
You're welcome, Roger.

Pete

On Dec 21, 11:22 am, Roger H.
wrote:
Yes. I had tried that particular approach, Pete. But it did not count all
the
valid occurrences in rows where there were only "one" count of a number
occuring three times. I have all the rows conditionally formatted so that
they will be easy to spot --- ( Formula is ) : =COUNTIF($A1:$IV1,A1)=3.
Maybe
I'll have to settle for an approximation with what formulas I have. Thank
you.



"Pete_UK" wrote:
Have you tried it like this? :

=SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)+COUNTIF($A2000: $IV2000,$A2000:$IV2000)=3*,1,0))/
3

Hope this helps.

Pete

On Dec 21, 7:55 am, Roger H. wrote:
Hello. Thanks for reading my question. My problem is directly related
to
Excel 2003's column limitation of 256. Situation: I have the following
array
formula in a cell, which counts the number of numerical entries that
occur
three times in the range from A1:IV1 ....
=SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)=3,1,0))/3. The formula is
replicated
downward for 1,000 rows. This works fine. I have several sets of these
one
thousand blocks of numbers (separated by blank rows), and therein
comes my
pain. The second block of cells begins at A2000. I now need to count
two rows
to see how many "three occurrences" there are in ( A1:IV1,
A2000:IV2000........B2:IV2,A2001:IV2001......) . In other words, I
want the
formula to 'see' these two ranges as one, if possible. I cannot simply
apply
the formula to the second block of cell rows because, as an example, a
number
might occur "two times" in A1:IV1 and "one time" in A2000:IV2000 and
would
not get counted in the sum. Thank you.- Hide quoted text -

- Show quoted text -

After readng your post again, I think the formula I suggested will do what
you want.

=SUMPRODUCT(--(FREQUENCY((A1:G1,A5:G5),(A1:G1,A5:G5))=3))

It treats (A1:G1,A5:G5) as a single range.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Not sure I understand what you want but somehting like this might work:

=SUMPRODUCT(--(FREQUENCY((A1:G1,A5:G5),(A1:G1,A5:G5))=3))

--
Biff
Microsoft Excel MVP


"Pete_UK" wrote in message
...
You're welcome, Roger.

Pete

On Dec 21, 11:22 am, Roger H.
wrote:
Yes. I had tried that particular approach, Pete. But it did not count all
the
valid occurrences in rows where there were only "one" count of a number
occuring three times. I have all the rows conditionally formatted so that
they will be easy to spot --- ( Formula is ) : =COUNTIF($A1:$IV1,A1)=3.
Maybe
I'll have to settle for an approximation with what formulas I have. Thank
you.



"Pete_UK" wrote:
Have you tried it like this? :

=SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)+COUNTIF($A2000: $IV2000,$A2000:$IV2000)=3*,1,0))/
3

Hope this helps.

Pete

On Dec 21, 7:55 am, Roger H. wrote:
Hello. Thanks for reading my question. My problem is directly related
to
Excel 2003's column limitation of 256. Situation: I have the
following array
formula in a cell, which counts the number of numerical entries that
occur
three times in the range from A1:IV1 ....
=SUM(IF(COUNTIF($A1:$IV1,$A1:$IV1)=3,1,0))/3. The formula is
replicated
downward for 1,000 rows. This works fine. I have several sets of
these one
thousand blocks of numbers (separated by blank rows), and therein
comes my
pain. The second block of cells begins at A2000. I now need to count
two rows
to see how many "three occurrences" there are in ( A1:IV1,
A2000:IV2000........B2:IV2,A2001:IV2001......) . In other words, I
want the
formula to 'see' these two ranges as one, if possible. I cannot
simply apply
the formula to the second block of cell rows because, as an example,
a number
might occur "two times" in A1:IV1 and "one time" in A2000:IV2000 and
would
not get counted in the sum. Thank you.- Hide quoted text -

- Show quoted text -