# Multiple Rolling Excel Sumifs

Hi-
I’m somewhat new to Knime and have hit a wall. I’m trying to use Knime to do what I can do in Excel. I want to calculate SUMIFS based on multiple criteria.

Criteria to use:
Customer #
Product Code

I want to calculate trailing 30 day stockouts based on Customer/Product/Date.

I’ve looked at multiple examples of group by, column aggregator or loops and I can’t seem to figure this out.

I’ve attached an example of the data as well as the desired outcome.

Thanks for any help. Share.xlsx (14.5 KB)

Question on SUMIF came up a couple of times in the forum. @ipazin created an example for this and shared it in this thread: Sumifs Excel - #14 by ipazin

@elsamuel also provided an example for this: Sumif and Conditional formatting ideas - #2 by elsamuel

2 Likes

Thank You @Marten_Pfannenschmidt for the example workflow. Where I am getting stuck is the Sum IFS to sum between dates. I’ve attached a sample workflow that I was using to achieve this, however its resulting in an empty table.

OR

Does this need to be setup with a rule based filter within a loop?

Sumifs Example.knwf (19.2 KB)

you compare ticket date < ticket date so basically the cell value with itself. This can never be true

@Daniel_Weikert, Understood, but how would one do a sumif based on a date range?

Hello @DataWing,

and welcome to KNIME Community!

You should loop over your criteria (use Group Loop Start node to define grouping columns) and in each iteration perform your calculation. Check workflow example where I got your desired outcome. Though not sure I got your logic with dates properly.
Sumifs Example_ipazin.knwf (40.6 KB)

Br,
Ivan

1 Like

Hi @ipazin-
Thank you so much for the help! Your workflow is a big help! But I am still not able to get the desired outcome based on the date logic.

Date Logic: Trying to sum the Stock Out column based on Prior 30 days from date of “TicketDate” by Customer/Product.

I re-did my work flow based on your logic, but still not able to calc the prior 30 days. For example, see excel file.

Example:
For Customer 4680 on Ticket Date 4/7/2021, the trailing 30 day range is until 3/8/2021. So therefore the Prior 30 Day Stock out should be 1. Current Knime workflow is still calc 4.

share export1.xlsx (12.7 KB)
Sumifs Example_rework.knwf (31.7 KB)

Hello @DataWing,

then seems you need to go row by row, filter one month data based on it and sum it. Check out workflow attached:
Sumifs Example_rework_ipazin.knwf (83.2 KB)

Br,
Ivan

2 Likes

Thanks @ipazin. This worked for me. I was also able to learn a lot through your workflow as well.

3 Likes