Count number of students passed minimum level

[timpie]

Hello all,

I am looking for an elegant solution to this next problem. I want to count the
number of students that have reached the minimum language level in dutch language.
Therefor I set up an array with levels in increasing level order. To get to the result
I compare each cell in colum C with the minimum level (set by selecting Minimum level).

My Solution only works (using sumproduct and match) when every cell in the test
range (column End Level) is filled in (excel match function constraint).
In reality the column (End level) is only filled in when an end test has been done so
I cannot help this (this column is also filled in by someone else) .. I cannot alter the
structure of the excel file so I cannot add any helper columns.

Does anyone have any idea on how to solve this ?
See example .. the example works but if you delete one of the values in the column C
you get an #N/B error.

Example1.xlsx

[XOR LX]

Hi.

You could use an array formula** version of your formula, though with COUNT:

=COUNT(1/(MATCH(End_Level,Dutch_Level,0)>MATCH(H5,Dutch_Level,0)))

which will ignore any errors resulting from empty cells.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[timpie]

Thankx!!!! XOR LX ... that's what I was looking for. I knew I had seen this before but couldn't find it.
Very simple solution

[XOR LX]

You're welcome! But it was you who did the hard part!

Cheers.

[Arkadi]

Another solution, now useless... is to add one more row at the top of "Dutch_Level" with a value of 0 in the cell. This would make the existing formula continue to work.

[timpie]

Thankx all!

Both solutions work ... however forgot to mention most important part. This count has to be performed ONLY on
visible cells. An advanced filter is in use almost all the time and with both solutions every row in the range is
considered. I have similar formulas for which I found VBA function Isvisible handy ( see below ) which can be used in
most of my sumproduct functions (e.g. =SUMPRODUCT(--(IsVisible(Rijbewijs)*(Rijbewijs="B")))
but this function doesn't seem to work with any of your solutions. I realize that this makes it more difficult but any ideas
are welcome ... or should I revert to writing another VBA function to do the count.


Public Function IsVisible(InRange As Range) As Boolean()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' IsVisible
' This function returns an array of Boolean values indicating whether the
' corresponding cell in InRange is visible.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim R As Range
Dim Arr() As Boolean
Dim RNdx As Integer
Dim CNdx As Integer

ReDim Arr(1 To InRange.Rows.Count, 1 To InRange.Columns.Count)
For RNdx = 1 To InRange.Rows.Count
For CNdx = 1 To InRange.Columns.Count
Set R = InRange(RNdx, CNdx)
If R.EntireRow.Hidden = True Or R.EntireColumn.Hidden = True Then
Arr(RNdx, CNdx) = False
Else
Arr(RNdx, CNdx) = True
End If
Next CNdx
Next RNdx
IsVisible = Arr
End Function

[XOR LX]

You can use this variation:

=COUNT(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A12)-MIN(ROW(A2:A12)),)),1/(MATCH(End_Level,Dutch_Level,0)>MATCH(H5,Dutch_Level,0))))

By the way, if blank cells are a possibility within End_Level, are you aware that this will mean that your dynamic definition for this range will fail?

Try deleting the contents of a couple of random cells from C2:C12 and then see what range is highlighted for End_Level.

Regards

[timpie]

Thankx ... this is above my comprehension. Anyway it doesn't seem to work. It returns 0 (also entered as matrix formula).

You are also right about the dynamic definition! PFff, **** happens.

[XOR LX]

Works for me. Perhaps you can re-post your workbook showing this result of 0?

Regards