Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 03-29-2017, 05:02 PM
OCM OCM is offline Windows 7 32bit Office 2000
Novice
 
Join Date: Mar 2013
Posts: 8
OCM is on a distinguished road
Default 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
File Type: png Sample.png (25.0 KB, 12 views)
Reply With Quote
  #2  
Old 03-29-2017, 05:55 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,647
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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]
Reply With Quote
  #3  
Old 03-29-2017, 06:14 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,647
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
[MS MVP - Word]
Reply With Quote
  #4  
Old 03-29-2017, 07:24 PM
OCM OCM is offline Windows 7 32bit Office 2000
Novice
 
Join Date: Mar 2013
Posts: 8
OCM is on a distinguished road
Default

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,
Reply With Quote
  #5  
Old 03-29-2017, 07:47 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,647
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by OCM View Post
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]
Reply With Quote
  #6  
Old 03-30-2017, 01:37 PM
OCM OCM is offline Windows 7 32bit Office 2000
Novice
 
Join Date: Mar 2013
Posts: 8
OCM is on a distinguished road
Default

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,
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate the progress per day pm1110 Excel 2 09-16-2015 02:34 AM
Group records - multiple IF stmts - Text After Group baum12 Mail Merge 1 05-25-2015 12:57 AM
Group Table Rows/Column (like Excel Group) eoinymc Word 1 03-11-2014 04:51 AM
calculate age userman Excel 8 06-02-2012 10:59 PM
How to calculate percentages??? fabthi Excel 3 04-13-2012 12:08 PM


All times are GMT -7. The time now is 10:47 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft