#1




Group IDs, calculate
Greetings,
Given the attached sample, I would like the to calculate the following: 1. Sum ‘Amounts’ (column J)> $8,265.83 2. Sum ‘Units’ (column I) >59.00 3. Sum of ‘Amounts’/Sum of ‘Units’ > $8,265.83/59 =$140.10 4. Subtract 24 from total units (step #2 above) > 59 – 24 = 35 5. Finally, multiply (step #3) by (step # 4) above >$140.10 * 35 = $4,903.5 So, total for Provider #001 = $4,903.5 Once I obtained totals for each provider, I would like to display the grand total for all. TIA, 
#2




Getting the amounts for each provider is as simple as:
=SUMIF(A2:A20,A2,J2:J20) where 20 is the last data row. Note that this does not require the data to be sorted. Doing the same for the units is equally straightforward. What is not apparent from your post is whether the 24 is to be deducted from each provider's unit count or only the ones for whom there is a unit value of 24, or if the deductible varies by provider. For provider 001, the formula is: =(SUMIF(A2:A20,A2,J2:J20)/SUMIF(A2:A20,A2,I2:I20))*(SUMIF(A2:A20,A2,I2:I20)24)
__________________
Cheers, Paul Edstein [MS MVP  Word] 
#3




Crossposted at: http://www.tektips.com/viewthread.cfm?qid=1776012
For crossposting etiquette, please read: http://www.excelguru.ca/content.php?184
__________________
Cheers, Paul Edstein [MS MVP  Word] 
#4




Paul,
Thanks for the reply post. I’ll apply your formula and post back. To clarify further about the 24 unit (24hrs. or 1day): it is to be deducted from each provider whom there is a unit. In fact, all of them have a unit value. The goal in the end is to identify providers who are over the 24 units. Regards, 
#5




Quote:
=MAX((SUMIF(A$2:A$20,A2,J$2:J$20)/SUMIF(A$2:A$20,A2,I$2:I$20))*(SUMIF(A$2:A$20,A2,I$ 2:I$20)24),0) and copied to each 'total recovery amount' row.
__________________
Cheers, Paul Edstein [MS MVP  Word] 
#6




Paul,
There were few that gave me negative values which I deleted manually. I did apply your latest formula to get some practice. Many thanks, Regards, 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Calculate the progress per day  pm1110  Excel  2  09162015 02:34 AM 
Group records  multiple IF stmts  Text After Group  baum12  Mail Merge  1  05252015 12:57 AM 
Group Table Rows/Column (like Excel Group)  eoinymc  Word  1  03112014 04:51 AM 
calculate age  userman  Excel  8  06022012 10:59 PM 
How to calculate percentages???  fabthi  Excel  3  04132012 12:08 PM 