# Calculating Inventory Sell Through by month based on multiple criteria

1. ## Calculating Inventory Sell Through by month based on multiple criteria

I am trying to calculate inventory sell through by month, but am running into a lot of issue. Below is a sample data set. The month will only reach 100% sell through when the quantity sold equals the amount received in a month. In the below sample, you see that 2014-04 achieved 100% sell through because 1 was sold in April, 2 in May, and 7 in June (equaling 10). 2014-05 achieves 87% sell through because there were 5 sold in July, 4 in August, 3 in Sept, and 1 in October (equaling 13). The other issue is that there would be 200,000 rows in the Excel sheet that contain multiple different SKU's, so the formula would have to look at each SKU and calculate only on the group of those similar SKU's. Please advise if I can provide any additional details.

HTML Code:
``351846``

2. ## Re: Calculating Inventory Sell Through by month based on multiple criteria

How about posting a sample workbook?

3. ## Re: Calculating Inventory Sell Through by month based on multiple criteria

Here is a test file

``Please Login or Register  to view this content.``

Bump......

5. ## Re: Calculating Inventory Sell Through by month based on multiple criteria

Sorry I have to make a pass on this one, at least for now. The task is interesting and well defined so it should spark some interest here on the forum. It may however be more of a VBA task, it is rather advanced.

6. ## Re: Calculating Inventory Sell Through by month based on multiple criteria

Should I post this in the VBA section also?

7. ## Re: Calculating Inventory Sell Through by month based on multiple criteria

No need to Jacc has put it before the "Cavalry"

If someone doesn't solve this in the interim - I'll have a look at it tomorrow

8. ## Re: Calculating Inventory Sell Through by month based on multiple criteria

Don't know if this helps or not but with a few helper columns and an array formula I got this to work for your data set.

1. Please check with your extended data set though because it was only tested on that set
2. I know the purists on this forum don't like helper columns and some of these could be reduced by incorporating into formulas (I think) but I left it open for you to check easily and frankly it would take a brighter person than me to try and get them all into one formula
3. I had to use an array formula so remember cntl+shift+enter

Good luck

Example(1).xlsx

9. ## Re: Calculating Inventory Sell Through by month based on multiple criteria

Try this - in G6 enter:
=IF(E6=0,G5,MEDIAN(0,1,(SUMIF(\$D\$6:\$D\$19,D6,\$F\$6:\$F\$19)-SUMIF(D\$5:D5,D6,E\$5:E5))/E6))
and fill down.

10. ## Re: Calculating Inventory Sell Through by month based on multiple criteria

Crooza/Romper, both of these work...However, they are quite the resource hog. I have 200,000 rows of data and Excel keeps stalling trying to calculate the formulas.

11. ## Re: Calculating Inventory Sell Through by month based on multiple criteria

Can your data be sorted by SKU?

12. ## Re: Calculating Inventory Sell Through by month based on multiple criteria

It is, It's sorted by SKU and then by Date.

13. ## Re: Calculating Inventory Sell Through by month based on multiple criteria

See if this version works better - note the two additional columns A and B - these could be incorporated into the formula if necessary.

14. ## Re: Calculating Inventory Sell Through by month based on multiple criteria

Here's a VBA solution:

``Please Login or Register  to view this content.``

15. ## Re: Calculating Inventory Sell Through by month based on multiple criteria

Here's a quicker version - but the example I saw only went from column C to Column G and from row 4 to row 18??

``Please Login or Register  to view this content.``

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