Getting the Sum from months

[noreaster]

I have 3 columns A-part numbers, B-Date (year, month, date) and C-quantity.
What I'm trying to figure out is how to get a Qty for Part for a month for
each month and do the same for each part. The sum would go to a cell.
ie
Part Date Qty
123 08 10/10 15
124 08 10/10 10
123 08 10/11 20


Some parts may repeatd in the same month. I have figured out how to get the
total quantity but trying to break in it down in months has me somewhat
stumped.

TIA

 

[Mike H]

Hi,

I'm not sure I understand but try this

=SUMPRODUCT((A2:A20=123)*(MONTH(B2:B20=1)*(C2:C20)))

This will sum Column C if the part number is 123 and the month is 1 (Jan)

In practice I'd use cell references and have the part number and month in a
cell.

Mike

 

[noreaster]

That worked for the most part, the MONTH didn't work correctly, what it gave
me was the total qty for the Part#. The way I have the date appears as "08
9/04" year month day. I would also need it by year.

I'm learning this on my own. Thanks for the help your giving me.

 

[noreaster]

I played with the formula and this works
=SUMPRODUCT((A2:A1000=123)*((MONTH(B2:B1000)=1)*(C2:C1000)))
I'm still trying to figure how to seperate by years

 

[Pete_UK]

I suppose your dates are true dates for the MONTH function to work, so
you could do it this way:

=SUMPRODUCT((A2:A1000=123)*(MONTH(B2:B1000)=1)*(YEAR(B2:B1000)=2008)*(C2:C1000))

or like this:

=SUMPRODUCT((A2:A1000=123)*(TEXT(B2:B1000,"mmmyy")="Jan08")*(C2:C1000))

Both would check for January 2008, but you would need to change the
formulae for other months - better to use cells to hold the month and
year so that the formula does not need to be modified.

Hope this helps.

Pete

 

[Peo Sjoblom]

Same way


YEAR(B2:B20)=2007


for instance

--


Regards,


Peo Sjoblom

 

[Noreaster]

The first one works great, the 2nd one dont, could be at my end.
Thank you very much.
Now I'm trying to use it to get the data from another sheet.