How to total numbers separated by commas in multiple cells.

Our take

If you're managing a spreadsheet for patient discharges and need to total room numbers listed as comma-separated values in cells E19:E23, you're not alone. This task can be tricky, especially when formulas yield unexpected results, like defaulting to "1" instead of "0" when there are no discharges. Fortunately, with a bit of guidance, you can simplify this process. Let’s explore a reliable formula that accurately counts discharges, ensuring you get the correct total while enhancing your spreadsheet skills.

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(.)=.]”))

submitted by /u/AnxiousTransitNut
[link] [comments]

Read on the original site

Open the publisher's page for the full experience

View original article →

Tagged with

#Excel alternatives for data analysis#natural language processing for spreadsheets#generative AI for data analysis#cloud-based spreadsheet applications#rows.com#AI formula generation techniques#modern spreadsheet innovations#machine learning in spreadsheet applications#enterprise-level spreadsheet solutions#digital transformation in spreadsheet software#Excel compatibility#collaborative spreadsheet tools#AI-driven spreadsheet solutions#automation in spreadsheet workflows#no-code spreadsheet solutions#AI-powered spreadsheet#formula generator#Excel alternatives#spreadsheet API integration#spreadsheet