# SUMPRODUCTIF with date in a table?

1. ## 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?

Alma

2. ## 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.

3. ## 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. ## 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.

5. ## Re: SUMPRODUCTIF with date in a table?

Here it is! Thanks again.

6. ## Re: SUMPRODUCTIF with date in a table?

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

7. ## 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. ## Re: SUMPRODUCTIF with date in a table?

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

Is this ok?

10. ## Re: SUMPRODUCTIF with date in a table?

See the attached file.

I changed the lay out.

Lowest value + % * next level.

See the attached file.

11. ## Re: SUMPRODUCTIF with date in a table?

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

#### 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