SUMIF Criteria

[Caitlin]

I am trying to create a table that represents when items are ordered, scheduled for distribution, and how many are in use or delivered at any given time -- while taking into account variables such as production lag and the total service life.

For Row 5, Items Delivered, it is easy to do a cumulative sum, but I am having difficulty trying to figure out how to exclude units that are more than x years old. For the sake of the example, the Service Life is set to 5, but I need for that to be able to change and be automated with the system.

If there a way to do a SUMIF statement and use a variable as the criteria?

 

[Faseeh]

Can you provide an example of the calculation?

 

[Somendra Misra]

Hi,

Not fully understood, but see file row 5.

Regards,

 

[Caitlin]

Faseeh, I am sorry that I did not get your response earlier. Somendra Misra got close (and thank you so much for your response!), but the table would need to be dynamic for any variables changed. For example, if the number of items ordered changed at any given time, the rest of the table would automatically update too.

 

[Somendra Misra]

@Caitlin

Would ask you to show the working that what cell you would change and how it will effect the table.

Regards,

 

[Caitlin]

@Somendra Misra that was my fault; not your work! I failed to include my distribution schedule equation in the dummy data. See attached. I believe that your solution works perfectly.

 

[Somendra Misra]

@Caitlin

Is this is SOLVED?

Regards,

 

[Caitlin]

@Somendra Misra Yes, thank you very much for your time and assistance!

 

[Somendra Misra]

You are Welcome!!!

Regards,