I have a worksheet where the columns represent types of medication and the rows are individual people.
I need to total how many people have each combination of 12 medications. For example, how many have the combination of med1, med2 and med10; or the combination of med2, med5, med6, med7 and med8; etc. Just examples.
See attached partial data file (hope it uploaded).
Thanks for any advice, really appreciate the help!
If I have interpreted correctly find in the attached a helper table in L1:U10.
Then this array formula in M2 filled down and across. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:
=IF(ROWS(M$2:M2)<=MATCH(M$1,$L$2:$L$10,0),"",SUM(--(MMULT($B$2:$J$100,TRANSPOSE((M$1=$B$1:$J$1)+($L2=$B$1:$J$1)))>1)))
Another interpretation: Also find this formula in K2 filled down with the sum in K1.Formula:=--(SUM(B2:J2)>1)
Dave
Thanks Dave. I think the array might work, but I'm struggling to interpret the results.
Does the helper table give the total for each medication pair or the total for each combination of all 9 medications observed?
What I'm aiming for is something like:
1. pi + booster + nnrti only = n persons
2. pi + booster + insti only = n persons
3. nnrti + insti only = n persons
4. sinsti only = n persons
etc. giving the sum of observations for all distinct combinations of the 9 medications, without regard to order.
Yes it gives total for each intersecting pair or combinations. If filters are applied the results will confirm.Originally Posted by mistert
To get a sum of each combination:
I would have no idea how to lay that out. If I have it right this formula array entered says there are 511 possible combinations. That sounds too conservative to me.Formula:=SUM(COMBIN(9,MATCH(B1:J1,B1:J1,0)))
Then you have to count the number of people for those. I would have no ideas how to lay that out or calculate it either.
OR do a separate table for each patient ... all 99.
Stay with the thread for a few days. There is a multi-talented, knowledgeable crew here on the forum. Some likely know if there is a practical solution and how to do it.
Hello mistert,
I wonder if my answer helps you. First I did an UnPivot of your data to make it into a better (for me) table. Then doing a Pivot Table on sheet2, you can filter which meds you want to see who has them and also their counts. No formulas needed as I'm lazy and did the whole process using built-in Excel tools.
Unpivot then counts by filter columns.xlsx
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Wow, Pivot tables look very useful. Glad to know about them!
Got me thinking a bit and came up with a partial solution that I bet someone can easily complete.
On the same worksheet, I used Advanced Filter to copy only unique records to the right of the original data, Table 1. So out of my total 266 patients, there are 53 unique combinations of the 9 medications represented by each row in Table 2.
This is close. Now what I need is to sum how many observations in Table 1 match each of those 53 unique patterns in Table 2, so I added a sum column but not sure how to count matching row patterns.
New doc uploaded.
in u3
=COUNTIFS(B:B,L3,C:C,M3,D:D,N3,E:E,O3,F:F,P3,G:G,Q3,H:H,R3,I:I,S3,J:J,T3)
the most possible combinations if drug levels are not given is 2^9 =512 but as you can not have a regime with no medication 511 as FlameRetired got to
Last edited by davsth; 05-30-2018 at 08:10 AM.
That did! Thanks to everyone for the help!!!
Hi misert,
Is this workbook the correct answer?
Do the totals add up?
CountifS Answer.xlsx
Yep, that's exactly what I got using your formula. I double checked about 4 of the combinations and the totals are correct. Thanks man!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks