Hello. I am going a bit nuts and looking for help. I am creating a spreadsheet for my hospital. There is a column which lists the room numbers of patients being discharged in cells E19:E23 (simple 1-2 digit numbers which may repeat in different units) separated by commas. I am trying to total the number of discharges based off of this.
The closest formula I have found follows, but it defaults to “1” instead of “0” if there are no discharges. I am a novice and basically just googling stuff so far and have tried so many formulas. I’ve figured out a dozen others in this sheet but this one still stumps me. Any help would be appreciated. Thank you!
=counta(filterxml(“<x><a>”&substitute(textjoin(“,”,true,e19:e23),”,”,”</a><a>”)&</a></x>”,”//a[number(.)=.]”))