Calculating sum of relative cells

[nxc]

Hey guys. Feel like i'm spamming the forum a bit but the more I work with Excel the more I realize that I'm not that good at it Anyways, I'm in need of a quite advanced formula (for me) that sums up relative relative rows of a specified column. The rows relation depends on the ID-number of the person. Look at my attachment.

In Sheet1 I have an extract of the data I'm working with. It's a medical record of different people in a study. The ID (col. B) seperate the different people. One person can have several perscriptions (spelling?) of medicine. Every perscription has an "fddd", a daily dose (col. L). I want to add up all the daily doses for every seperate ID of my data in Sheet2. So for example, for ID1 I want it to sum L3:L6 in Sheet1 and return it to Sheet2!B2. If the ID is not found I want it to be blank. Cheers

[DonkeyOte]

Check out the SUMIF function in XL Help

Sheet2!B2: =SUMIF(Sheet1!B:B,A2,Sheet1!L:L)

You will get a zero using the above where ID not found... if you want to return blank either use a custom format of

[=0]"";#,###

Or you have to use a MATCH

Sheet2!B2: =IF(ISNUMBER(MATCH(A2,Sheet1!B:B,0)),SUMIF(Sheet1!B:B,A2,Sheet1!L:L),"")

[Macdave_19]

You Need to use:

Please Login or Register  to view this content.

Here

[nxc]

Wow thanks guys fast response, works excellent. Used this to get rid of blanks:

Please Login or Register  to view this content.