Need help with scrap file!

[jcrowe38]

Okay... So I am making a excel file to track the scrap from our machines. Operators manually input the dates and the amount of scrap in the appropriate category to the right of the date.

How do I sort the data they input to sum up the scrap for each category on a per weekly/monthly basis on another sheet? The dates are inputed as 6/28/2012 for example and the scrap is just a numerical number of say 50. Each column of scrap is side by side so im assuming whatever formula is used for one I can use for the next. This scrap list is continued through the year and all days of the months are together and thats why i need to sort with the dates.


Thanks!
Joe

[SteveG]

Hi,

Can you post a sample file?

Thanks,
Steve

[jcrowe38]

here you go... I hope this is helpful and you can help me!

Thanks!

[SteveG]

Hi,

In F3,

=SUMPRODUCT(--(MONTH($A$3:$A$93)=ROW(A1)),--($B$3:$B$93))

Copy down. The "ROW(A1)" returns the 1 for January and as copied down increases (2, 3 etc...). If you need to accomodate for the year you can also do that using the YEAR function.

HTH
Steve

[jcrowe38]

Great!

Thank you very much Steve!

[jcrowe38]

If I wanted to do this weekly or daily... how would I do that? Seems like I cant just replace monthly with weekly like you can with yearly.


Thanks.