Finding Monthly Usage

[Guest]

I have been hacking away at this for a while, and I feel that I'm at a dead
end on my own, any help is greatly appreciated.

I have records of parts and the dates and quaity of usage. I am trying to
total the number of each part used for a month, and then have something like
a pivot table showing each part listed down, months listed across, and
quantity used in the data. There is a record for each time the part is used.
Here is a small sample of my data.

Part # Description
Issue Date TRL_QTY
401083 Head Stop Brush, Folder 11/9/05 3
401083 Head Stop Brush, Folder 11/13/05 3
401083 Head Stop Brush, Folder 2/13/06 8
401083 Head Stop Brush, Folder 2/13/06 -2
401115 Oven Gasket, P4
11/26/05 12
401137 Blade, Tidland Slitter System, Press 43 12/6/05 2
401138 Blade, Tidland Slitter System, Press 43 POWDER 6/1/05 2

 

[Guest]

Hi

I reckon the pivot table is the way to go. When you create your pivot table,
drag Part to the right hand side and drag Date to the top. You can then
select Description as the data and use Count. Once you have done this,
right-click on the date row and select Group and then select Months (as well
as Year, if you like).

Hope this helps
Andy.

 

[Bob Phillips]

In another sheet, list the products from A2, down, months in B1 across, and
in B2 enter

=SUMPRODUCT(--(Sheet2!$B$2:$B$20=$A2),--(MONTH(Sheet2!$C$2:$C$20)=MONTH(B$1)
),Sheet2!$D$2:$D$20)

and copy down and across.

For the months in B1 across, eneter full dates (like 01/01/2006, 01/02/2006,
etc.), just format as mmm if you want.

--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

 

[Guest]

Absolutely perfect Andy. I was trying grouping and pivot tables independent
of each other. Sometimes we make things difficult based on how difficult we
expect it to be... Thanks a ton!

 

[Guest]

Thanks for the feedback - glad you're sorted.

Andy.