Summing multiple entries ONLY when there are multiple entries in a row

[jsto7380]

Dear community,


I have a set of data where for a given visit I usually have a single entry for a given drug.


On occasions, there are multiple entries in rows, one after the other: I need to sum the doses. In addition, each entry may correspond to a number of days treated.


I have used to columns F and G to try and figure out when to start and when to stop summing: F=0, G=1 to start and F=1, G=1 to stop -- not sure if this is useful. Ideally, I would like to be able to filter these entries, something which I cannot do with this F/G solution. Next, I would need a formula to do the work from the start to the stop position (dose x days of treatment), with the solution placed in the row corresponding to the first entry (eg F=0, G=1).


I'm sure this is straight forward but I haven't been able to crack it or find a way to do so on the internet.


Here is the data file: http://sdrv.ms/Q4wYIX


Many thanks in advance,

 

[Luke M]

Hi jsto7380!

I'm afraid I can't access files from my location. Would it be possible to post a small sample of what the workbook looks like? Sorry for the inconvenience.

 

[jsto7380]

Hi Luke M! Thank you for the prompt reply


I've copied and pasted some lines here - I hope that formatting wont be an issue and you could copy and paste the same lines into Excel...


If that doesn't work, let me know what you think I could do the show you the data

Many thanks,

[pre]

Subject No.	Pairing No.	Visit Code	Date of visit	Category	Multi entries	Mulit entries 2	Dose	Date 1st dose	Date end of administration	Days of treatment	Total Dose
143	43	0	23.9.2006	TAC	0	0	4	7.11.2002	28.10.2006	1452
143	43	0	23.9.2006	MPA	0	0	1000	27.6.2001	28.10.2006	1950
143	43	0	23.9.2006	corticoid	0	0	40	23.12.2002
143	43	2	13.7.2001	basiliximab	0	0	20	27.6.2001	27.6.2001	1
143	43	2	13.7.2001	IV corticoid	0	1	250	27.6.2001	28.6.2001	2	??
143	43	2	13.7.2001	IV corticoid	1	1	70	29.6.2001	5.7.2001	7
143	43	2	13.7.2001	CSA	0	0	375	27.6.2001
143	43	2	13.7.2001	MPA	0	0	2000	27.6.2001
143	43	2	13.7.2001	corticoid	0	0	35	6.7.2001
143	43	4	27.9.2001	CSA	0	0	300	27.6.2001
143	43	4	27.9.2001	MPA	0	0	2000	27.6.2001
143	43	4	27.9.2001	corticoid	0	0	10	6.7.2001
143	43	5	24.12.2001	CSA	0	0	300	27.6.2001
143	43	5	24.12.2001	MPA	0	0	2000	27.6.2001
143	43	5	24.12.2001	corticoid	0	0	7,5	6.7.2001
143	43	7	20.6.2002	CSA	0	0	250	27.6.2001
143	43	7	20.6.2002	MPA	0	0	2000	27.6.2001
143	43	7	21.6.2002	corticoid	0	0	0	6.7.2001	16.7.2001	11
143	43	8	23.12.2002	CSA	0	0	0	27.6.2001	6.11.2002	498
143	43	8	23.12.2002	TAC	0	0	8	7.11.2002
143	43	8	23.12.2002	MPA	0	0	1000	27.6.2001
143	43	8	23.12.2002	IV corticoid	0	1	600	6.11.2002	6.11.2002	1	??
143	43	8	23.12.2002	IV corticoid	1	0	500	7.11.2002	7.11.2002	1
143	43	8	23.12.2002	IV corticoid	1	0	300	8.11.2002	8.11.2002	1
143	43	8	23.12.2002	IV corticoid	1	0	200	9.11.2002	9.11.2002	1
143	43	8	23.12.2002	IV corticoid	1	0	150	10.11.2002	10.11.2002	1
143	43	8	23.12.2002	IV corticoid	1	0	60	11.11.2002	19.11.2002	9
143	43	8	23.12.2002	IV corticoid	1	0	50	20.11.2002	26.11.2002	7
143	43	8	23.12.2002	IV corticoid	1	1	40	27.11.2002	3.12.2002	7
143	43	8	23.12.2002	corticoid	0	1	30	4.12.2002	21.12.2002	18	??
143	43	8	23.12.2002	corticoid	1	0	20	11.12.2002	22.12.2002	12
143	43	8	23.12.2002	corticoid	1	1	17,5	23.12.2002
143	43	9	7.7.2003	TAC	0	0	6	7.11.2002
143	43	9	7.7.2003	MPA	0	0	1000	27.6.2001

[/pre]

 

[Luke M]

I think I got most of that. Having trouble with cols F:J

I see there's a "Category" column with words like CSA, MPA, corticoid, etc.


Then, are there 3 columns of numbers? What are the headers? I see that F and G are your trigger columns, but not sure what col H is supposed to be.


Then, a column for "Date 1st dose", and I think I got the rest setup correct. Now, back to figuring out what your actual question is. =)


EDIT: Nevermind, I figured out the formatting. Working on a solution now...

 

[Luke M]

Formula to determine total dose with multiple lines codes as you have them:

=IF(AND(F2=0,G2=1),SUMPRODUCT(($D$2:$D$35=D2)*($E$2:$E$35=E2)*($H$2:$H$35)),"")


Note that col F and G not really needed for this formula. You could just do:

=SUMPRODUCT(($D$2:$D$35=D2)*($E$2:$E$35=E2)*($H$2:$H$35))

and it should give you total dose for that drug on that day.

 

[jsto7380]

Thank you very much Luke M!


I have one more question to add because your solution, although perfect, wont always fit my data. The problem is that Date of Visit is an artificial entry, and is not always entered correctly.


What I need to do is to identify when there are multiple entries in a row: I would then use this instead of column D in your SUMPRODUCT formula.


This is what I tried to do with columns F and G:


F is:


=IF(E2=E1;1;0)


G is:


=IF(F2=F3;0;1)


To mark all the entries that are multiple with a '1', I thought to make another column (say H), filter G for '1' and indicate in H, then filter F for '1' and fill blanks in H.


This is a rather frumpy solution - perhaps there is a better/simpler way??


I would also like to know if its possible to do something similar as the SUMPRODUCT solution, but to determine an average value (eg dose in the above example)


Thank you once again, and congratulations on achieving your milestone 3000 posts!!!

 

[Luke M]

Hi jsto7380!


Thanks for the kind words. I'm afraid I got a little lost on what exactly the new col G would be indicating. However, I think you just need some sort of logic check. Let's change the formula in col F to this:

=IF(E2=E1,F1,E2&COUNTIF(E$2:E2,E2))

We now have a unique identifier for each group of drugs, where we can gather "sets" based on 1 words. With the formula in col F, I believe we could use this SUMPRODUCT formula:

=IF(F2<>F1,SUMPRODUCT(($F$2:$F$35=F2)*($H$2:$H$35)),"")

 

[jsto7380]

Thanks a lot! The first formula is cool, and would work with the sums above. The second formula didn't work for me, I got back a message about being in a circular function or something along those lines...


I was going for 1s and 0s to eventually be able to filter the entries. The G column was because with the formula in F the first line of multiple entries would be a 0. I'm sure theres a way to get these 1s and 0s but your solution would work just fine!

 

[Luke M]

Not sure why the circulaar error popped up, but it sounds like things are working okay.

 

[jsto7380]

Hi Luke, sorry for the late response - I had very sillily entered the second formula in column F. When I figured this - all your formulas clicked and I finally really understood what was going on.

Thank you, the solution was perfect