CODEWITHSUNDEEP
excelgoogle-sheetsspreadsheetfinanceaccounting
Alex B
Alex B

Reputation: 1

How to use SUMIFS for Recurring payments based on frequency?

I'm currently working on a payment plan to automatise placing a recurring payment on the right date. On the sheet List! I have a list of all the different cost categories and on sheet Plan! I have dates with different week dates which those payments should be falling in.

My simplified (they're much longer than this) tables look like this more or less:

List! sheet

Column A Column B Column C Column D Column E
Car rental Rentals Monthly 10/06/2024 500,00
Crane rental Rentals Monthly 07/06/2024 1.500,00
Tax advisor Professional Fees Bi-monthly 15/06/2024 2.000,00
Engineering consultant Professional Fees Quarterly 24/04/2024 3.000,00
Water Utilities Quarterly 13/02/2024 500,00
Electricity Utilities Monthly 28/06/2024 1.200,00

Column A has the detailed cost, column B the cost Category/Account that the SUMIFS will be evaluating, Column C the frequency of the payment and Column D the last date that we know (this is the part that gets tricky as I want to evaluate the same day of the month based on which month it lands), and column E is the value that is paid according to Column C

Plan! sheet

Column A Column B Column C Column D Column E Column F
27/07/2024 03/08/2024 10/08/2024 17/08/2024 24/08/2024
Week 30 Week 31 Week 32 Week 33 Week 34
Rentals 0,00 0,00 2.000,00 0,00 0,00
Professional fees 2.000,00 2.000,00 0,00 0,00 3.0000,00
Utilities 1.000,00 0,00 0,00 0,00

On the Plan! sheet I've added a first row with the last weekday of each week so that the SUMIFS become "easier" to make the evaluation. I should be receiving the total sum per category based on each week. I've added the totals that I've manually calculated based on the above example into the table.

I have tried the following formula so far, the first part works but then when evaluating the dates I get an error, I believe there should be a better function for this (example for cell Plan!C3 above):

=SUMIFS(List!E1:E6;
        List!B1:B6;
        Plan!$A3;
        List!C1:C6;
        "Monthly";
        DATE(YEAR(Plan!B$1);MONTH(Plan!B$1);DAY(List!D1:D6));
        ">"&Plan!C$1;
        DATE(YEAR(Plan!B$1);MONTH(Plan!B$1);DAY(List!D1:D6));
        "<="&Plan!D$1
       )

This is only trying to even get the monthly payment, I don't even know where to start to do it quarterly or bi-monthly.
I'd rather stay away from VBA, I'm ok with having an individual formula that I have to drag through the cells.
All help will be appreciated! If there's already a post on this feel free to refer it!

Upvotes: 0

Views: 159

Answers (1)

Wen
Wen

Reputation: 1

SUMIFS does not support the use of array functions directly within its criteria. What I mean by this is, this will never work:

DATE(YEAR(Plan!B$1);MONTH(Plan!B$1);DAY(List!D1:D6));">"&Plan!C$1;

Because of this:

DAY(List!D1:D6)

The function has no way of 'knowing' what DAY(List!D1:D6) means, as far as it's concerned you are asking "turn D1:D6 into a day value", which of course is invalid.

The SUMIFS function doesn't allow for direct manipulation of ranges within the criteria, such as using DAY to filter values.

The simplest example to show that with this data set would be

=SUMIFS(E1:E6;DAY(D1:D6);"<"&D2)

As you can see if you try it, that doesn't work.

Instead, you'll have to nest functions or extract the value you want. One option is ARRAYFORMULA.

Rewritten with ARRAYFORMULA this function looks something like:

=SUM(
  ARRAYFORMULA(
    IF(
      (List!$B$1:$B$6 = Plan!$A3) *
      (List!$C$1:$C$6 = "Monthly") *
      (DATE(YEAR(Plan!C$1);MONTH(Plan!C$1);DAY(List!$D$1:$D$6)) > Plan!C$1) *
      (DATE(YEAR(Plan!C$1);MONTH(Plan!C$1);DAY(List!$D$1:$D$6)) <= Plan!D$1);
      List!$E$1:$E$6;
      0
    )
  )
)

With this, I get 2000 where you want it, in Plan!C3.

Basic explanation: ARRAYFORMULA lets you do formulas on elements of arrays. Then we check IF the category name is the given category. If it's monthly. The dates according to how you wrote them (I suspect something is off here) Then, if all conditions are met for a given element, the value is included in List!E1:E6 to be summed up. Otherwise, send off a 0.

Then sum up all the elements in List!E1:E6 that match.

Please be aware that I don't think this is complete still. However, it should put you on your way to getting where you want to go.

Upvotes: 0

Related Questions