# Solved Excel formula to calculate based on IDs Microsoft Excel 2013 32/64-bit - license...
March 29, 2017 at 16:51:17
Specs: Windows 7
 Greetings, Given the following columns:Column A: Column I Column J Column PProvider ID Units Amounts Total recovery amount I wold like to enter a formula on Column P to calculate the following: 1. Group Provider IDs and sum ‘Amounts’ (column J) 2. Group Provider IDs and sum ‘Units’ (column I)3. Sum of ‘Amounts’/Sum of ‘Units’ 4. Subtract 24 from total units (step #2 above) 5. Finally, multiply (step #3) by (step # 4) above Once I obtained totals for each provider, I would like to display the grand total for all.Any ideas?Also, can you please advise how I can attach a sample? TIA, See More: Excel formula to calculate based on IDs March 30, 2017 at 11:40:28
 You can check for "Total Units" that are less than 24 by adding an IF function. This will eliminate negative Recovery values.=IF(SUM(I2:I4)<24,"",(SUM(I2:I4)-24)*(SUM(J2:J4)/SUM(I2:I4)))

#1 March 29, 2017 at 17:59:45
 You can't attach a file in this forum, but you can upload a generic file to zippyshare.com and then post a link back here in the forum.By "generic" I mean no personally identifiable information, confidential information, etc. Since the file will public, you should be careful about what it contains.Steps 3, 4 and 5 I get. Steps 1 and 2 are confusing, at least to me.

Report •

#2
March 29, 2017 at 18:31:58
 Thank you.In case the link below doesn't work, below is a sample to clarify #1 & #2 Column A ……….Column I Column JProvider ID……… Units Amounts 001 15.00 \$2946.74 001 24.00 \$2032.43 001 20.00 \$3286.661. \$2946.74 + \$2032.43 + \$3286.66 = \$8,265.832. 15+24+20 = 59here are links to the my sample: http://www45.zippyshare.com/v/b3XiQ...[url=http://www45.zippyshare.com/v/b3XiQuon/file.html][img=http://www45.zippyshare.com/thumb/b3XiQuon/file.html][/img][/url]TIA, message edited by ocm1

Report •

#3 March 29, 2017 at 19:42:12
 You have a couple of options...I notice that you do not have the same number of Provider Id's for each ID. i.e. 3 each 001, 2 each 002, 1 each 003, etc. That makes it hard to write a single formula that can be copied into each "Recovery" field because you are dealing with different sized ranges.You could write an individual formula in each of your merged cells that look something like this:(You didn't provide Row numbers, so I am assuming that your data starts is Row 2)For ID 001: =(SUM(I2:I4)-24)*(SUM(J2:J4)/SUM(I2:I4))That will give you the \$4,903.46 that you have in your sample.Now you need to write a similar formula for ID 002 and reference the ranges in Rows(5:6).Then another one for ID 003 referring to only Row(7).As you can see, you need to edit each formula to reference a specific range based on the number of ID Rows for each ID.Another option is to create a list of Unique ID's in another column, e.g. O and then put this formula in P2 and drag it down to bottom of your unique list. (The Advanced Filter feature on the Data ribbon can help you create that list of Unique ID's)=(SUMIF(A:A,O2,I:I)-24)*(SUMIF(A:A,O2,J:J)/SUMIF(A:A,O2,I:I))I do have a question though. You said:4. Subtract 24 from total unitsWhat happens if the total units is less than 24? You'll be multiplying by a negative number, which will give you a negative Recovery value. Is that an issue?

Report •

Related Solutions

#4
March 30, 2017 at 10:36:40
 Thank you, this works for what I’m trying to do. To answer your question regarding what happens if the total unit is less than 24….I’m only interested values that are over 24 units (I day) that the provider charges. We are responsible only for 24 units. Does this make sense?I appreciate all your help!

Report •

#5 March 30, 2017 at 11:40:28 