Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-08-2016, 03:36 PM
phillipsdp phillipsdp is offline Sum based on multiple criteria (one of the criteria is not unique) until a threshold is reached. Windows 7 64bit Sum based on multiple criteria (one of the criteria is not unique) until a threshold is reached. Office 2010 64bit
Novice
Sum based on multiple criteria (one of the criteria is not unique) until a threshold is reached.
 
Join Date: Nov 2016
Posts: 1
phillipsdp is on a distinguished road
Post Sum based on multiple criteria (one of the criteria is not unique) until a threshold is reached.

Hi -

I'm trying to build an output model similar to the one shown below, please see the following explanations to help answer:

Sample Source Data Explanation:

(I) The service date represents each transaction date for each customer
(II) The Unique ID represents a particular customer


(III) The product code input, (in this example 2 but can be 1, 2, or both) indicates whether or not the 1st threshold input (in this example $300) should be deducted from the total cost when calculating net cost to me.
(IV) The total cost represents the total cost of each transaction

Other important considerations: The source data is sorted by service date and unique ID, so that each customers transactions are listed in the order in which they were incurred.

Sample Output Explanation:

* For each unique ID I need to calculate the net cost to me on each day of the year based on the inputs shown below: Explanation of Inputs:

(I) Product code- the product code can be either 1, 2, or both. In this example it is 2 and denotes when a customer transaction has a threshold amount that needs to be applied before subsidies are factored.

(II) 1st threshold- given the product code input of (1, 2, or both) the total cost of the transactions for the customer should be reduced by this amount until the total cost with the associated product code input has been exhausted. (Example: Unique ID 1x1's first transaction has product code 2 listed and a total cost of $2,944.47 (far exceeding the first threshold, however there could be multiple transactions before the threshold is met, see Unique ID 3x3 from the sample output). The $2,944.47 should be reduced by the 1st threshold amount of $300 and the remainder should be further reduced by the subsidy amount (in this example is 25%) to show a net cost to me on 1/2/2015 of $1,983.35. Further, the threshold input can be $0 to $400.

(III) Subsidy- this is the amount that should be used to reduce the total cost of each transaction. In this example when product code input equals 1 or when total cost of product code 2 has surpassed the 1st threshold the subsidy amount should be applied to reduce total cost. IMPORTANT: Subsidy can be expressed as either a percentage of total cost or as dollar value reduction of total cost.

Main Issues:
(I) When a unique ID has multiple transaction with a selected product code input before meeting the selected threshold amount.
(II) Once threshold is met further reducing the total cost by the subsidy.

Please note from the sample output, unique ID example 3x3 which encounters its first transaction on 1/1/2015 with a product code of 1 for $5, because the product code is 1 only the subsidy is used to reduce the total cost of this first transaction. The second transaction occurs on 1/7/2015 with a product code of 2 for $235, because this product code is equal to our input we must first reduce this claim by the threshold amount of $300 (Note: this does not cause a negative balance instead it should be treated as the 1st step in surpassing our 1st threshold). The third transaction on 1/9/2015 with a product code of 2 for $1,363.20 should be reduced by the remaining amount of our 1 threshold balance after the prior transaction on 1/7/2015 of $65 ($300 - $235) and then further reduced by the subsidy input.
Attached Files
File Type: xlsx Example.xlsx (13.8 KB, 9 views)

Last edited by phillipsdp; 11-08-2016 at 03:39 PM. Reason: Added Example Attachment
Reply With Quote
  #2  
Old 11-09-2016, 12:53 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Sum based on multiple criteria (one of the criteria is not unique) until a threshold is reached. Windows 7 64bit Sum based on multiple criteria (one of the criteria is not unique) until a threshold is reached. Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Hi
please do not cross post without links.
read http://www.excelguru.ca/content.php?184 to understand what it's all about
http://www.mrexcel.com/forum/excel-q...d-reached.html
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

Tags
help please, sumifs, threshold

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Select value from table based on two criteria gebobs Excel 4 07-21-2016 08:26 AM
Sum based on multiple criteria (one of the criteria is not unique) until a threshold is reached. Count unique values that match 2 or more criteria caeiro01 Excel 1 10-25-2015 02:34 AM
Sum based on multiple criteria (one of the criteria is not unique) until a threshold is reached. I need to add multiple values based on multiple criteria in a cell not sure what to do AUHAMM Excel 3 10-27-2014 09:11 PM
Matching Criteria against a range when criteria is not in contiguous cells?? GMorris Excel 9 08-20-2014 02:15 AM
transpose values based on mulitple criteria mlttkw Excel 1 12-06-2013 02:00 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:31 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft