sumproduct?

[Jim_Shu]

I'm trying to filter data into a cell that meets certain criteria...

I would like to count the number of times a sku is found in each region in each month... daily inventory counts are recorded.. the date is recorded as MM/DD/YYYY...

is sumproduct my solution? I'm getting errors, specifically #NAME?

=sumproduct((sheet1!L:L=SKU)*(sheet1!M:M=Region)*(sheet1!C:C>=1/1/2009)+(sheet1!C:C<=1/31/2009))

 

[Pericic]

..cannot use the whole column as a range
also sku or any text must be in quotation marks, i.e., "SKU"
also, when using a date it must be preceded by date, i.e., C1:C100<=Date(2009,1,31)

 

[jasonb75]

Try
=sumproduct((sheet1!L:L="SKU")*(sheet1!M:M="Region")*(sheet1!C:C>=1/1/2009)+(sheet1!C:C<=1/31/2009))

Not sure if this is exactly what you're looking for but it should clear the #Name? error. Post back if it gives wrong results.

JB<!-- / message -->

 

[iliace]

Are SKU and Region defined names? Double-check that.

Also, I don't think you can use full column references (such as L:L) unless you're using Excel 2007.

 

[barry houdini]

.....also dates won't be recognised in that format....and you don't want the +, that will give you some odd results. Try like this

=SUMPRODUCT(--(sheet1!L$2:L$1000=Y2),--(sheet1!M$2:M$1000=Z2),--(sheet1!C$2:C$1000>="1/1/2009"+0),--(sheet1!C$2:C$1000<="1/31/2009"+0))

where Y2 is a specific SKU and Z2 a specific region

You might also want to replace the "hardcoded" dates with cell references......

 

[Fazza]

Hi. Would a pivot table be suitable? Doesn't need any formulas. HTH, Fazza

 

[shemayisroel]

Jim,


The below white paper should help with SUMPRODUCT

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Jim_Shu]

No joy... still does not work... I did try the pivottable.. and while that puts it into a better format to use a data filter...it filters by day... if I could find a way to search a column and change the data to JAN, FEB, MAR, etc... based on the entered date of MM/DD/YYYY.. I'd be in good shape... well... at least exploring a different area anyways....

 

[barry houdini]

Which formula did you try, Jim?

You could extract month and year into another column like this

=TEXT(C2,"mmm-yy")

 

[Fazza]

Jim,

Fro the pivot table, grouping by month can be applied to the date field.

For example, see http://www.contextures.com/xlPivot07.html

HTH, Fazza