Get Unique days per MD per patient

Hi all:

Enclosed Excel file where I find the unique patients per MD using this array formula (enter has array):

Code:

---

=SUM(IF(FREQUENCY(IF($B$2:$B$7=$E2,MATCH($A$2:$A$7,$A$2:$A$7,0)),ROW($A$2:$A$7)-ROW($A$2)+1),1))

---

Now I am trying to get the unique stay days per patient per MD using this array (which not work - result #value!):

Code:

---

=SUM(IF(FREQUENCY(IF(AND($A$2:$A$7=$F2,$B$2:$B$7=$E2),MATCH($C$2:$C$7,$C$2:$C$7,0)),ROW($C$2:$C$7)-ROW($C$2)+1),1))

---

Please see enclosed Excel 2010 file to see if you can help me to correct this array enter formula.

Thanks

Victor

Can you repost your sample showing full layout and non-working formula.

Hi:

Please see post with enclosed sample array enter formula.

Thanks

Victor

What defines a unique stay day? For arada, there are 2 patients with 57 days total, but as there are no days I cannot see what unique constitutes.

Extend your example to duplicate a patient for one particular MD and tell us what you expect to get.

xld

Enclosed (unique md2.xlsx) extended example to duplicate a patient for one particular MD and what I expect to get.

Thanks for the help.

Victor

What defines a unique stay day?

Do not repeat same patient for MD. For example
X patient A MD 2 days
X patient A MD 2 days

The unique days is 2 days, since is the same patient for the MD and the patient stay is only 2 days total not 4 in the hospital. Is repeat since this patient has 2 different laboratories not shown in the data base.

Correction if file name columns header in J1

Changes: Should say "Correct result for column I" instead of "Correct result for column H"


Thanks

Victor

See if this works on the whole sheet as it seems to work with the data you gave. ARRAY ENTERED:

Code:

---

=SUM(($B$2:$B$17=$E2)*$C$2:$C$17*(ROW($A$2:$A$17)=(MATCH($A$2:$A$17&$B$2:$B$17,$A$2:$A$17&$B$2:$B$17,0)+1)))

---

I am attaching the worksheet where you will find this formula in Col I.

Hi Shrivallabha

Brilliant use of the concatenation ranges in the match function.

That is the solution I was looking for.

Bye, until next time.

Thanks all for the interest in help me.

Happy holidays to all!

Victor

Thanks to this forum and MrExcel where I have seen great formula solutions being provided. Its all taken from here.
Wish you happy holidays. Have fun :beerchug: