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,

Attached Images

Sample.png (25.0 KB, 19 views)

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
[Fmr MS MVP - Word]

Cross-posted at: http://www.tek-tips.com/viewthread.cfm?qid=1776012
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

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,

In that case, I suspect you don't want -ve values, either, which is what you'd get for a provider whose unit total was, say, 20. You can set those to display as 0 via:
=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
[Fmr MS MVP - Word]

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,