Excel Formula Range Limitation
 

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.

Excel Formula Range Limitation
 

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.

Excel Formula Range Limitation
 

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.

Excel Formula Range Limitation
 

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 -

Excel Formula Range Limitation
 

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 -

Excel Formula Range Limitation
 

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 -

Excel Formula Range Limitation
 

Yes. It worked. I froze the column where I had your formula and and panned
the rest of the spreadsheet past it with the right arrow. Each formatted
"three occurrence" number lined up with where the formula was saying there
was an occurrence. Thanks again to the two of you gentlemen! ( My spreadsheet
is beautiful again )

"T. Valko" wrote:

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 -

Excel Formula Range Limitation
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Roger H." wrote in message
...
Yes. It worked. I froze the column where I had your formula and and
panned
the rest of the spreadsheet past it with the right arrow. Each formatted
"three occurrence" number lined up with where the formula was saying
there
was an occurrence. Thanks again to the two of you gentlemen! ( My
spreadsheet
is beautiful again )

"T. Valko" wrote:

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 -