Counting Ranges

  • Thread starter Thread starter CGI
  • Start date Start date
C

CGI

I would like to count a range if all the numbers in that range meet a stated
criteria. For example I have 50 columns (so 50 ranges) and I want to see how
many of these cloumns contain numbers that are all above 75.

e.g. Column 1 has 76, 76, 78, 90 so that would be counted, Column 2 has 77,
76, 94, 83 so that would be counted also, Column 3 has 72, 76, 77, 79 so that
would not be counted and the total would be 2.

Hope that is somehow understandable - I am a bit of a beginner!
 
One way...

Assuming the 50 columns are in the range A1:AX4 and each column contains 4
numbers.

=SUMPRODUCT(--(COUNTIF(OFFSET(A1:AX4,,COLUMN(A1:AX4)-COLUMN(A1),4,1),">75")=4))
 
Back
Top