Microsoft Office Forums Group IDs, calculate
 Register FAQ Search Today's Posts Mark Forums Read

#1
03-29-2017, 05:02 PM
 OCM Windows 7 32bit Office 2000 Novice Join Date: Mar 2013 Posts: 8
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,
Attached Images
 Sample.png (25.0 KB, 11 views)
#2
03-29-2017, 05:55 PM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 16,319

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
03-29-2017, 06:14 PM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 16,319

__________________
Cheers,
Paul Edstein
[MS MVP - Word]
#4
03-29-2017, 07:24 PM
 OCM Windows 7 32bit Office 2000 Novice Join Date: Mar 2013 Posts: 8

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
03-29-2017, 07:47 PM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 16,319

Quote:
 Originally Posted by OCM 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.
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
[MS MVP - Word]
#6
03-30-2017, 01:37 PM
 OCM Windows 7 32bit Office 2000 Novice Join Date: Mar 2013 Posts: 8

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 Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post pm1110 Excel 2 09-16-2015 02:34 AM baum12 Mail Merge 1 05-25-2015 12:57 AM eoinymc Word 1 03-11-2014 04:51 AM userman Excel 8 06-02-2012 10:59 PM fabthi Excel 3 04-13-2012 12:08 PM

All times are GMT -7. The time now is 09:32 AM.

 -- Default Style -- Mobile Style Contact Us - Privacy Statement - Top