PDA

View Full Version : Sum data if week number is <= current weeknumber



Panda
11-30-2010, 10:29 AM
Hi There,

I have a spreadsheet (attached) containing 2 tabs, one with a list of units and components needed to make them. The other containing just a list of the components needed and with the expected delivery date in weeks along the columns.

I am trying to get the total parts in stock on the first tab, to look at the current week number and only show how many components I have in stock up to that point. So for example in Week 49 for component 1, 20 components are coming in but due to the way that I have set up my formula my spreadsheet is returning that I have 35 components in stock.

I think I need to your SUMPRODUCT but cant seem to get the formula working. Can anybody help?

Thanking you in advance

Phil

austenr
11-30-2010, 11:22 AM
Your WEEKNUM is showing an error and is not formulated correctly. also you need to have the Analysis Toolpak installed to use WEEKNUM.

Bob Phillips
11-30-2010, 12:02 PM
Try

=SUMPRODUCT((Stock_List!$A$2:$A$12=Recovery_Plan!E$5)*(--(MID(Stock_List!$B$1:$F$1,FIND(" ",Stock_List!$B$1:$F$1)+1,99))=Recovery_Plan!$B$4)*(Stock_List!$B$2:$F$12))

Panda
12-01-2010, 02:25 AM
Try

=SUMPRODUCT((Stock_List!$A$2:$A$12=Recovery_Plan!E$5)*(--(MID(Stock_List!$B$1:$F$1,FIND(" ",Stock_List!$B$1:$F$1)+1,99))=Recovery_Plan!$B$4)*(Stock_List!$B$2:$F$12))

Thanks Xld it works a treat =:) Just out of interest what does the (--(MID mean and why at the end of the FIND(" ",Stock_List!$B$1:$F$1)+1,99) is there a +1,99?

I think I follow what the rest of the formula is doing but am sort of trying to learn from you guys when you help me out.

Thanks again

Phil

Bob Phillips
12-02-2010, 03:49 PM
The week number in Recovery_Plan!$B$4 is a standard number, but the value in row 1 is the text "Week " & weeknum, so we need to extract the numeric portion of that.

We use MID and FIND to get the week number by FINDing the space, and extracting 99 characters after that space + 1 character. We use 99 because any number equal to or larger than the actual number would work (in this case, 2 would probably suffice). So MID(rng,FIND(" ",rng)+1,99) will return 48, 49, 50, etc.

But MID still returns a string, so we coerce it to a number with --.