ADVERTISEMENT
On 2002-10-29 06:11, PaddyD wrote:
Given that the required max seems to be the max of several distinct ranges of cells (I7:I26,I29:I52,U3:U26,U29:U52
), how about the following, which is effectively equivalent to the other alternatives but is a little shorter to type:
1) select the distinct ranges all at once (by holding down the control key when you're doing it)
2) give the resulting selection a name in the name box to the left of the formula bar, e.g. maxrange
3) in a spare cell (e.g. C1), enter:
=max(maxrange)
4) calculate the desired count using a countif:
=COUNTIF(I7:I26,"="&C1)+COUNTIF(I29:I52,"="&C1)+COUNTIF(U3:U26,"="&C1)+COUNTIF(U29:U52,"="&C1)