How can I check for #Value! in a cell? ie If(c4 = #Value!, 0, 1).

Alternatively, is there a way of looking for the maximum value in a column
based upon a second column meeting a certain criteria?

The example is
1
2a
3
2b
2b
4
2c

I need to know that stream 2 has three splits (the double 2b entry is
intentional).

Thanks,
Brett