+ Reply to Thread
Results 1 to 11 of 11

SUMPRODUCTIF with date in a table?

  1. #1
    Registered User
    Join Date
    07-09-2014
    Location
    Athens, Greece
    MS-Off Ver
    2010
    Posts
    6

    SUMPRODUCTIF with date in a table?

    Good day to all,

    I've scoured the internet trying to find a formula that can solve my problem. Alas, I've come up empty handed.

    I need to find a way to calculate the total cost based on an annual amount paid per year. If the amounts paid are in column "G" and the year is in column "B" and the fee table per year and amount is as below, what formula can I used??

    Table.png

    For starters, I have tried the following, which does not account for the year, but I keep getting a zero value.

    =SUMPRODUCT(--(G13/$G$1)>$C$5:$C$9;((G13/$G$1)-$C$5:$C$9);$L$5:$L$9)

    I've also tried using multiple IF conditions, but that just ends up being too complicated!

    Any ideas?

    Thanks in advance,

    Alma
    Last edited by Almanaki; 07-10-2014 at 05:05 AM. Reason: Table didn't turn out correctly once posted

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,952

    Re: SUMPRODUCTIF with date in a table?

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    07-09-2014
    Location
    Athens, Greece
    MS-Off Ver
    2010
    Posts
    6

    Re: SUMPRODUCTIF with date in a table?

    Untitled.jpg

    Here is what the spreadsheet looks like. I've deleted some columns for simplicity. In which case, the formula would read
    =SUMPRODUCT(--(D12/$E$1)>$C$5:$C$9;((D12/$E$1)-$C$5:$C$9);$J$5:$J$9)

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: SUMPRODUCTIF with date in a table?

    @almanaki

    Please as request an excel file, instead of a JPG, in which we can't work with.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Registered User
    Join Date
    07-09-2014
    Location
    Athens, Greece
    MS-Off Ver
    2010
    Posts
    6

    Re: SUMPRODUCTIF with date in a table?

    Here it is! Thanks again.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: SUMPRODUCTIF with date in a table?

    And what should be the result, and based on what criteria

  7. #7
    Registered User
    Join Date
    07-09-2014
    Location
    Athens, Greece
    MS-Off Ver
    2010
    Posts
    6

    Re: SUMPRODUCTIF with date in a table?

    My apologies. Reading my original post I can see that my problem isn't very clear.

    What I need is a formula that will calculate the management fees, according to the net premium level, the year and the related percentage. The fee structure works like marginal income tax rates(meaning, the first 900 are charged a 55% fee, the next up to 1800 at 30%, and so forth). This is why I created column J with the marginal rates for year 1.

    The manual way of doing it would use this formula : =(($D$5*$E$1)-D12)*$E$6+($D$5*$E$1)*$E$5; and would require that you manually change the formula every time the year changes, or the premium changes level according to the table. The idea is to develop a formula that will account for the premium level and year on its own.

    The condition in the SUMPRODUCT formula works, but it doesn't provide a result, and it also doesn't account for the year.

    In year 1 the results should be 104,11, year 2 26,15, year 3 8,71, year 4 5,35, year 5 5,1, year 6 4,96, year 7+ 0.

    Also note, in the example provided the customer is paying quarterly, but the fee schedule is based on annual payments which is why E1 is in order to account for different payment structures (Annual, semi-annual, quartly).

    I hope that makes things clearer.

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: SUMPRODUCTIF with date in a table?

    Can you also add the results manualy in the excel sheet and post it?

  9. #9
    Registered User
    Join Date
    07-09-2014
    Location
    Athens, Greece
    MS-Off Ver
    2010
    Posts
    6

    Re: SUMPRODUCTIF with date in a table?

    Is this ok?
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: SUMPRODUCTIF with date in a table?

    See the attached file.

    I changed the lay out.

    The answers where not the same as you added.

    I made it like:
    Lowest value + % * next level.

    See the attached file.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    07-09-2014
    Location
    Athens, Greece
    MS-Off Ver
    2010
    Posts
    6

    Re: SUMPRODUCTIF with date in a table?

    Thanks for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Index Match Based on One Date Criteria, Table Contains Beginning Date and End Date
    By jcox1953 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-09-2014, 02:41 PM
  2. SUMPRODUCTIF Equivalent
    By dougmcc1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-09-2013, 03:52 PM
  3. Replies: 4
    Last Post: 10-05-2012, 03:06 PM
  4. Sumproductif
    By thawthorne in forum Excel General
    Replies: 3
    Last Post: 01-29-2009, 10:02 AM
  5. Creating a SUMPRODUCTIF Function
    By thawthorne in forum Excel General
    Replies: 1
    Last Post: 05-23-2008, 12:19 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1