Excel Formula Range Limitation

[Roger H.]

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.

 

[Pete_UK]

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

 

[Roger H.]

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]

You're welcome, Roger.

Pete

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.





- Show quoted text -

 

[T. Valko]

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


You're welcome, Roger.

Pete

 

[T. Valko]

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.

 

[Roger H.]

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]

You're welcome. Thanks for the feedback!