# How to extract max values from multiple rows and count how many fall to certain criteria, in one-cell function

I have a sheet in Excel that shows success of students in certain parts of the exam (tests, presentations, final exam etc.). I need to calculate some statistical data and the problem is that for some tests they have three tries, only the highest points count and I do not have the "max" column. I need to count how many got more than 15 points in best attempt, how many between 10 and 14,9, etc. Also I need to see how many students had at least one try (based on a non-empty field or >=0).

Table looks like this (one part):
Student....Att1....Att2....Att3
Anna.........10......14.....13,5
Belle.........7.........15......15,5
Clara........17......18.....16,5

So, from this I would need to extract in one cell that number of max per-row values >=15 is 2 (Belle at 15,5 and Clara at 18), and that total students ever attempted at least once is 3.

I repeat that I cannot add the separate column where I would calculate max values because it is an official document, and adding one to another sheet is not a preferred solutions.

I tried some subtotal and offset combinations, but without much sucess. I use MS Excel 2013, other colleagues use 2007 and 2010.

• If you can't add to this sheet, where are you planning to put the formula? – CharlieRB Mar 12 '14 at 11:58
• could you please show (based on this sample) the final expected array with all this extra info without any added column ? – NeronLeVelu Mar 12 '14 at 16:30

This formula can be easily expanded for any number of rows or columns

`=SUMPRODUCT((SUBTOTAL(4,OFFSET(B2:D2,ROW(B2:D10)-ROW(B2),0))>15)+0)`

`OFFSET` returns each row as a separate range, `SUBTOTAL` finds the max value [4] in each row and `SUMPRODUCT` counts how many of those values are >15.

It's also possible to do the same with `COUNTIF` instead of SUBTOTAL, i.e.

`=SUMPRODUCT((COUNTIF(OFFSET(B2:D2,ROW(B2:D10)-ROW(B2),0),">15")>0)+0)`

For a range, e.g. >10 and <=15 you can adjust the SUBTOTAL version like this

`=SUMPRODUCT((SUBTOTAL(4,OFFSET(B2:D2,ROW(B2:D10)-ROW(B2),0))>{10,15})*{1,-1})`

• How would you do a condition of between two numbers? – Madball73 Mar 12 '14 at 20:14
• Good question, Madball - I edited my answer – barry houdini Mar 12 '14 at 22:58
• Testing the above, looks like the between works. Also made me realize my method doesn't and should have actually used +--. Removing my answer. Yours also has advantage of being a single range instead of 3. Sweet! – Madball73 Mar 12 '14 at 23:21
• Madball73, thanks a lot for this. The formula works well and gives me desired data. There is only one strange case: I have students that tried the test, got 0 points, and students that did not try the test and have no points (empty cell), I also have to differentiate them. The formula you gave, with <{5,0})*{1,-1}) at the end (I needed >=0 and <5), counts also case when student has 3 empty cells, or 3 cells with some text like N/A, -. Ending it <5)+0) does the same. How can I avoid that and differentiate empty cell (or cell containing non-numeric value) and 0, or count solely 0? – adriatic Mar 13 '14 at 14:01

for the count of more or equel to 15 for a line (let say in columns E, so E2 here)

``````=COUNTIF(B2:D2;">=15")
``````

for you max it (in E5 here)

``````=countif(E2:E4;">0")
``````

sample

``````student at1 at2 at3     up15
anna    10  14  13,5    0
Belle   7   15  15,5    2
clara   17  18  16,5    3
Max    2
``````

could add a test for at least 3 try per row but not clear tome where you want it (per line or on max)

• There is a requirement where helper/additional columns cannot be added. – Madball73 Mar 12 '14 at 16:11
• but adding something will, by itself add a column (or row). Now is a hidden column available ? There is also 2 other solution 1) doing it from an external worksheet (not a clean solution) 2) make it from a macro than eventually use internal excel function (but is certainly more efficient with pure vbs code). – NeronLeVelu Mar 12 '14 at 16:17