function that will sum the total length of stay

**blendypops** · 05-16-2019, 03:19 AM

Morning.

I'm trying to create a function that will sum the total length of stay for a person in a given hospital ward. I've tried various iterations of sumif, sumifs, etc, but I can't seem to get it to work. In cell AA, I want to sum total length of stay for ICU, of which there was only one. However if "5th" (T4:V4) was also ICU, i'd want that to be summed into the total that will appear in AA, if that makes sense?

Any worldly individuals out there that can see how to overcome this?

Thanks a million for your help in advace

blendypops

Attachment 624620

**Pepe Le Mokko** · 05-16-2019, 03:21 AM

Hi and welcome
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**AliGW** · 05-16-2019, 03:23 AM

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. You are new here, so I've done it for you this time.)

**Don Toto** · 10-13-2020, 03:26 PM

This post still open so I assume there's no answer yet.
The following formula for cell AA4 seems to work

=SUM(IF($H4=AA$3,$J4,0),IF($K4=AA$3,$M4,0),IF($N4=AA$3,$P4,0),IF($Q4=AA$3,$S4,0),IF($T4=AA$3,$V4,0),IF($W4=AA$3,$Y4,0))

The basic idea is that each IF function (there are 6 of them) returns either the length of stay or 0. We take the sum of 6 numbers and that's the solution of "ICU" (actually AA$3)

I've put $ at the "right" places so that the formula can be copied across to give the right answer for NSD, 3E, etc. And it can be copied down to compute for other rows.

I'm sure this could be simplified but I haven't worked on it further. Seems to be straightforward and works for the spreadsheet you provided.

Hope this helps.

**blendypops** · 10-14-2020, 09:11 AM

I can't lie, this spreadsheet hasn't been used in about a year. However, thank you so much for your answer, the function works well and I will definitely be using it in other projects going forward. Thanks a million

function that will sum the total length of stay

LinkBack

Thread Tools

Rate This Thread

Display

function that will sum the total length of stay

Re: sumifs, but maybe not?

Re: function that will sum the total length of stay

Re: function that will sum the total length of stay

Re: function that will sum the total length of stay

Thread Information

Users Browsing this Thread

Similar Threads

multiple criteria in sumifs - sum(sumifs({}...)) not working without ctrl+shift+enter

[SOLVED] Sumifs formula without using sumifs....

[SOLVED] If no SUMIFS matches, 1, otherwise the SUMIFS result...

sumifs vs. Application.WorksheetFunction.SumIfs

[SOLVED] Looking for something simpler than SUMIFS - SUMIFS

SUMIFS Puzzle - Trying to avoid adding multiple SUMIFS to get valid result

[SOLVED] Sumifs, problem with the formula: =sumifs(c10:c200,<=today(),0)

Bookmarks

Bookmarks

Posting Permissions