SUMIFS - ">" or "<"

[DD1]

Hi,

I am having problems with a SUMIFS formula whereby the formula references a column which is either "<=21" or ">21". Just to give it some context - this relates to the number of days a patient is in hospital.
The formula works for "<=21", but not for ">21".
It will make more sense when you open the file.

Does anyone know why the formula does not work?

Many thanks
David

[FlameRetired]

No. I cannot tell why it does not. It doesn't appear to be a character set issue because MATCH and VLOOKUP work fine.

In the meantime if you need something to do the job try this

Please Login or Register  to view this content.

Edit Standby. I'll see if I can get us some help.

[DD1]

Thanks Dave. My actual formula has other criteria, which is why I didn't use the VLOOKUP function. I just wanted to simplify it when I posted my question. Thanks for replying though.

[FlameRetired]

Hold on. I see the problem. SUMIFS is interpreting the text of C4 the same as if you had entered =SUMIFS(K:K,J:J,">"&C4). That means it is trying to compare a number value 21 to a text value in J:J whose value is 0. 0 of course is not greater than 21.

It works for C3 because the syntax ≤ 21 is not evaluated as <=. To Excel it is just some more text that finds a match in J:J. You can demonstrate this by changing the formula in C3 to =IF(B3>21,"> 21","<= 21") and the entry in J3 to <=21. Now D3 also returns a 0 instead of 20.

You'll need a different approach to get SUMIFS to work here. Since I don't see the larger picture I'm not of much help there. Hopefully this will give you some ideas.

[FlameRetired]

DD1,

An afterthought.

Try changing the formula in C3:C4 to =IF(B3>21,"#> 21","≤ 21") (or with some more suitable extra leading character before the > 21). Then change J4 to #> 21. Your formula works now.

[AlKey]

This formula should work fine with your original setup

In D3 and copy down

Please Login or Register  to view this content.

Adjust ranges as needed.

[DD1]

Thank you Alkey. That's a very neat solution.

My actual spreadsheet/formula though requires 2 criteria. I have modified my spreadsheet and re-attached it. Would you mind taking another look? I did have a go myself without success unfortunately.

It also has in excess of 400,000 rows. Does that make any difference to the Excel functions that you would use - i.e. are some more efficient than others?

Many thanks
David

[DD1]

Thanks Dave. That is an alternative and I may end up doing that. Alkey was able to use the SUM and INDEX functions which I like, but I have 2 criteria and wasn't able to modify his formula to include the additional criteria. I have re-attached the spreadsheet with the additional column. Hopefully he will be able to modify it for me. If not, I will use your solution.
Thanks for taking the time to look at it.
Regards,
David

[FlameRetired]

Piggybacking on AlKey's formula add the new criteria by making this adjustment.

Please Login or Register  to view this content.

[DD1]

Thanks to both of you for your help. That worked brilliantly.
Really appreciate it.
David

[FlameRetired]

You're welcome. Glad to help. Thanks for the feedback and rep.