Excel date/product count to specified limit

Question

Column A "Sales Dates", Column B "=A2-A1" for "Date Diff", Column C "Customer Name", Column D "Item", Column E "Items Ordered Count"

My issue is I have to do a running 30 day total for each customer to see that specific items are not being ordered above "x" number within any 30-day period.

Does anyone have any ideas?

Answer 1

I may not be fully understanding your question, but I don't think you can do what you ask in excel. This might be a situation where a database that can do SQL might come in handy.

The best I can come up with in excel is a Pivot Table, with the customers as rows, dates as columns (group by month), and sum of Items Ordered in the data area. Then conditional format the data area to highlight values > your limit.

Perhaps if you provide some sample data & output I can come up with something more like what you need.

Answer 2

The formula would look something like this:

{=SUM(IF((A$2:A2>=A2-29)*(D$2:D2=D2),E$2:E2,0))}

It should be entered into cell F2 and copied down to the last row of your data. I pasted in a test spreadsheet below so you can see where things go (sorry for the formatting--hopefully it will look better if you paste it into Excel).

IMPORTANT: This is an array formula, so after you type in the formula (and don't type in the braces {} when you do), you must press Ctrl-Shift-Enter instead of just Enter (see this link for more details).

What does the formula do? It does two loops:

• First, it loops through all the Sales Dates from the beginning of the log to the current row and checks if each date is between the date of the current row and 29 days earlier (which makes a 30-day window). (By "current row" I mean the row where the formula is located.)
• Second, it loops through all the Items from the beginning of the log to the current row and checks if there is a match with the Item of the current row.

For any row where both checks are true (the "*" in the formula does an "and" operation), Items Ordered Count is added to the sum, otherwise zero is added to the sum. So, when it's finished, you have a count for each row of how many orders there were in the past 30 days for that item.

HTH,

-Dan

Sales Dates Date Diff Customer Name Item Items Ordered Count 30-Day Count
1/1/2009    0 dfsadf 11336 70 70
1/2/2009    1 asdfd 10218 121 121
1/3/2009    1 fsdfjkfl 10942 101 101
1/6/2009    3 slkdjflsk 13710 80 80
1/7/2009    1 slkdjls 10480 127 127
1/9/2009    2 sdjjf 11336 143 213
1/11/2009   2 woieuriwe 11501 84 84
1/14/2009   3 owqieyurtn 10191 78 78
1/15/2009   1 weisd 10480 113 240
1/16/2009   1 woieuriwe 12024 133 133
1/17/2009   1 vkcjl 13818 125 125
1/20/2009   3 sdflkj 11336 128 341
1/23/2009   3 jnbkdl 10480 141 381
1/25/2009   2 pqcvnlz 10480 137 518
1/27/2009   2 hwodkjgfh 12878 80 80
1/28/2009   1 zjdnfg;pwlkd 10942 123 224
1/31/2009   3 zlkdjnf;psod 13173 93 93
2/2/2009    2 zlknpdodfg 11336 119 390
2/4/2009    2 zjhdfpwskjh 12004 57 57
2/5/2009    1 asdfd 10218 121 121
2/8/2009    3 fsdfjkfl 10942 101 224
2/11/2009   3 slkdjflsk 13710 80 80
2/14/2009   3 slkdjls 10480 127 405
2/16/2009   2 sdjjf 11336 143 390
2/18/2009   2 woieuriwe 11501 84 84
2/21/2009   3 owqieyurtn 10191 78 78
2/24/2009   3 weisd 10480 113 240
2/25/2009   1 woieuriwe 12024 133 133
2/27/2009   2 vkcjl 13818 125 125
2/28/2009   1 sdflkj 11336 128 390