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

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" wrote:

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

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.

"Mike H" wrote:

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" wrote:

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

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



"noreaster" wrote:

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.

"Mike H" wrote:

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" wrote:

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

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)*(YE AR(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

On Oct 10, 6:53*pm, noreaster
wrote:
I played with the formula and this works
=SUMPRODUCT((A2:A1000=123)*((MONTH(B2:B1000)=1)*(C 2:C1000)))
I'm still trying to figure how to seperate by years



"noreaster" wrote:
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.

"Mike H" wrote:

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" wrote:

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- Hide quoted text -

- Show quoted text -

Same way


YEAR(B2:B20)=2007


for instance

--


Regards,


Peo Sjoblom

"noreaster" wrote in message
...
I played with the formula and this works
=SUMPRODUCT((A2:A1000=123)*((MONTH(B2:B1000)=1)*(C 2:C1000)))
I'm still trying to figure how to seperate by years



"noreaster" wrote:

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.

"Mike H" wrote:

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" wrote:

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

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.

"Pete_UK" wrote:

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)*(YE AR(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

On Oct 10, 6:53 pm, noreaster
wrote:
I played with the formula and this works
=SUMPRODUCT((A2:A1000=123)*((MONTH(B2:B1000)=1)*(C 2:C1000)))
I'm still trying to figure how to seperate by years



"noreaster" wrote:
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.

"Mike H" wrote:

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" wrote:

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- Hide quoted text -

- Show quoted text -