SumIF with "<="

[CodeMonkeys]

I'm trying to make a dynamic form that will show QTY and Sales sums for current Week, MTD, YTD, and QTD. On the form I have week set up and working already with the formula =SUMIF('Raw Data'!$H:$H,$B$1&$B$2&$A$3&$A9&$C$1,'Raw Data'!$G:$G) Where as B1 & B2 are the year and month (Value will change based on combo box), A3 is the week # (1-5 and value will change based on combo box) A9 is the store name, and C1 is the product code (4001 is Food, 4002 is Beverage, etc). So in Raw data I have a column for each of these values and then in H:H I string them together to form the same string as what I'm asking for, if it matches then sum up the value in G:G (In this case qty). So that works perfectly for the current week, what I'm trying to do is =SUMIF('Raw Data'!$H:$H,$B$1&$B$2&"<="&$A$3&$A9&$C$1,'Raw Data'!$G:$G) so that it'll sum up every string that has a Week # value less than or equal to the one selected on the form (A3) so It'll give me MTD. So if in Raw Data the criteria I'm looking for is "2014February3StoreName4001" for the week, I want it to find "2014February(less than or equal to 3)StoreName4001" for the MTD.

I hope this all makes sense.

Thanks for the help!

 

[shawnhet]

Your concantenation method won't work with <=, but you should be able to adapt the SUMIFs function

SUMIFS function - Excel - Office.com

The syntax for the "<=" in a Sumifs criteria would be "<="&A3.

Cheers,

 

[CodeMonkeys]

Got it! Was simpler than I thought...go figure right? Thank you!

 

[shawnhet]

Good to hear. Thanks for the feedback!