failing with MAX and IFs

Hi and welcome to the MrExcel Message Board,

I don't know. It looks OK to me.




Have you defined your named ranges correctly? I defined mine to be the shaded areas in the extract above.

Hi, thanks for the quick reply.

I checked to ensure my named ranges are correct. In fact, I typed in the addresses (like, U2:U6494) and it replaced it with the ranges so I'm going to say that those are fine. And actually when I look, the cell that results the 14.8 is a cell that should not even be included since for that particular individual, UnitsTransfused is blank.

However I noticed that if I don't Control+Shift+Enter the formula (which I notice you did not), I get a #VALUE error. Does that offer any clues or does that just tell us what we already know - I am using arrays?

mishelli said:

UnitsTransfused is blank.

Click to expand...

Does the cell contain a blank or is it empty? Empty counts as zero but blank does not.

And, yes, I should be using arrays but it did not make it to the display - sorry about that.

The formula posted by RickXL returns the correct result only because it is the first value. Change U6 to >0 and the result does not change. Enter the formula as an array and it works for me.

Try this, as an array, change the range to suit
=MAX(IF((C2:C10<>"u")*(A2:A10=0)*(A2:A10<>""),C$2:C$10,0))

RickXL said:

Does the cell contain a blank or is it empty? Empty counts as zero but blank does not.

And, yes, I should be using arrays but it did not make it to the display - sorry about that.

Click to expand...

I'm not very savvy about blank vs empty vs null but to my knowledge the cells are blank. In any event, since I'm trying to evaluate the Max value, isn't that moot?

If I put 888 in cell W11 the maximum value goes to 888.
If I now put a blank or space character into cell U11 the maximum value reverts to 12.7.

konew1 said:

Try this, as an array, change the range to suit
=MAX(IF((C2:C10<>"u")*(A2:A10=0)*(A2:A10<>""),C$2:C$10,0))

Click to expand...

Ok that fixed it! Thanks so much for the help. And for edu-ma-cation purposes, does anyone know why the blanks are causing problems in that formula?

Also as a followup question, I seem to be also failing at counting. http://i.imgur.com/ZsHi843.png
I'm trying to count the number of registered people who were transfused.

In cell W6616 I have =COUNTIFS(UnitsTransfused,">0",UnitsTransfused,"<>0",Registered,"=y")

I subsequently filtered column U (Units Transfused) to show results greater than 0 and Column M (Registered) to show results equal to "Y." Then in U6616 the formula I employed was: =SUBTOTAL(2,U2:U6594)

Now my question is, why are there 95 results in one and 94 results in the other. I was OCD enough to count them individually and indeed, there are 95. So where is my 'lost' cell and how do I find it?

Please to help so far.
Sorry but I am not permitted to access http://i.imgur.com/ZsHi843.png from this machine