Count pricing associated to year
 

I have part prices (O8 - O23) and years they were purchased (C8 - C23) I want
to create a formula that adds up only parts purchased for X year.

Any suggestions

Thanks in advance
--
Neall

Count pricing associated to year
 

If you mean date in Column C use the below for number of parts purchased in
2009

=SUM(IF(YEAR(C8:C23)=2009,1,0))

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"Neall" wrote:

I have part prices (O8 - O23) and years they were purchased (C8 - C23) I want
to create a formula that adds up only parts purchased for X year.

Any suggestions

Thanks in advance
--
Neall

Count pricing associated to year
 

Say you want to calclate prices for items purchased in the year as indicated
in C11, then use =SUMIF(C8:O23,C11,O8:O23). Obviously, changing C11 to C
whatever, will change the result, it the other cell contains a different year.

--
HTH

Kassie

Replace xxx with hotmail


"Neall" wrote:

I have part prices (O8 - O23) and years they were purchased (C8 - C23) I want
to create a formula that adds up only parts purchased for X year.

Any suggestions

Thanks in advance
--
Neall

Count pricing associated to year
 

Thanks however, maybe I am missing something here

=SUM(IF(YEAR(C8:C23)=2009,1,0))

C8:c23 are the dates however there is no reference to the corresponding
pricing columns (O9:O28) to get the prices to add.

Basically if C9,C11,C15 = 2007 goto O9,11,15 and give me the sum.

Did I miss something?



--
Neall


"Jacob Skaria" wrote:

If you mean date in Column C use the below for number of parts purchased in
2009

=SUM(IF(YEAR(C8:C23)=2009,1,0))

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"Neall" wrote:

I have part prices (O8 - O23) and years they were purchased (C8 - C23) I want
to create a formula that adds up only parts purchased for X year.

Any suggestions

Thanks in advance
--
Neall

Count pricing associated to year
 

Neall

The earlier one returned the number of entries....Use the below to return
the sum

=SUMPRODUCT(--(YEAR(C8:C23)=2009),O8:O23)

If this post helps click Yes
---------------
Jacob Skaria


"Neall" wrote:

Thanks however, maybe I am missing something here

=SUM(IF(YEAR(C8:C23)=2009,1,0))

C8:c23 are the dates however there is no reference to the corresponding
pricing columns (O9:O28) to get the prices to add.

Basically if C9,C11,C15 = 2007 goto O9,11,15 and give me the sum.

Did I miss something?



--
Neall


"Jacob Skaria" wrote:

If you mean date in Column C use the below for number of parts purchased in
2009

=SUM(IF(YEAR(C8:C23)=2009,1,0))

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"Neall" wrote:

I have part prices (O8 - O23) and years they were purchased (C8 - C23) I want
to create a formula that adds up only parts purchased for X year.

Any suggestions

Thanks in advance
--
Neall

Count pricing associated to year
 

Thanks Jacob, but I think we are both missing each others points

I have rows 8 - 23

Column C8 - 23 has the year the parts were purchased
Column D8 - 23 has the part number that was purchased
Column E8 - 23 has the part name that was purchased
Column O8 - 23 has the price paid for that part in its corresponding row

So what I am looking for is to use column C to find the year (07,08,09,2010)
and then add up all the corresponding prices in Column O and give a result.





--
Neall


"Jacob Skaria" wrote:

Neall

The earlier one returned the number of entries....Use the below to return
the sum

=SUMPRODUCT(--(YEAR(C8:C23)=2009),O8:O23)

If this post helps click Yes
---------------
Jacob Skaria


"Neall" wrote:

Thanks however, maybe I am missing something here

=SUM(IF(YEAR(C8:C23)=2009,1,0))

C8:c23 are the dates however there is no reference to the corresponding
pricing columns (O9:O28) to get the prices to add.

Basically if C9,C11,C15 = 2007 goto O9,11,15 and give me the sum.

Did I miss something?



--
Neall


"Jacob Skaria" wrote:

If you mean date in Column C use the below for number of parts purchased in
2009

=SUM(IF(YEAR(C8:C23)=2009,1,0))

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"Neall" wrote:

I have part prices (O8 - O23) and years they were purchased (C8 - C23) I want
to create a formula that adds up only parts purchased for X year.

Any suggestions

Thanks in advance
--
Neall

Count pricing associated to year
 

Thanks this worked!

But now I have one problem, even thought the date format is showing 2008,
excel is reading it as (example) 39538 any suggestions?


--
Neall


"Kassie" wrote:

Say you want to calclate prices for items purchased in the year as indicated
in C11, then use =SUMIF(C8:O23,C11,O8:O23). Obviously, changing C11 to C
whatever, will change the result, it the other cell contains a different year.

--
HTH

Kassie

Replace xxx with hotmail


"Neall" wrote:

I have part prices (O8 - O23) and years they were purchased (C8 - C23) I want
to create a formula that adds up only parts purchased for X year.

Any suggestions

Thanks in advance
--
Neall

Count pricing associated to year
 

Thanks again for your time Jacob issue resolved now a new one on why excel is
reading my dates as simple 5 digit numbers.

--
Neall


"Jacob Skaria" wrote:

Neall

The earlier one returned the number of entries....Use the below to return
the sum

=SUMPRODUCT(--(YEAR(C8:C23)=2009),O8:O23)

If this post helps click Yes
---------------
Jacob Skaria


"Neall" wrote:

Thanks however, maybe I am missing something here

=SUM(IF(YEAR(C8:C23)=2009,1,0))

C8:c23 are the dates however there is no reference to the corresponding
pricing columns (O9:O28) to get the prices to add.

Basically if C9,C11,C15 = 2007 goto O9,11,15 and give me the sum.

Did I miss something?



--
Neall


"Jacob Skaria" wrote:

If you mean date in Column C use the below for number of parts purchased in
2009

=SUM(IF(YEAR(C8:C23)=2009,1,0))

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"Neall" wrote:

I have part prices (O8 - O23) and years they were purchased (C8 - C23) I want
to create a formula that adds up only parts purchased for X year.

Any suggestions

Thanks in advance
--
Neall

Count pricing associated to year
 

Neall

In P1 enter 2007, P2 = 2008, P3 = 2009, P4 = 2010

In Q1 enter the formula and copy that down as required
=SUMPRODUCT(--(YEAR($C$8:$C$23)=P1),$O$8:$O$23)


If this post helps click Yes
---------------
Jacob Skaria


"Neall" wrote:

Thanks Jacob, but I think we are both missing each others points

I have rows 8 - 23

Column C8 - 23 has the year the parts were purchased
Column D8 - 23 has the part number that was purchased
Column E8 - 23 has the part name that was purchased
Column O8 - 23 has the price paid for that part in its corresponding row

So what I am looking for is to use column C to find the year (07,08,09,2010)
and then add up all the corresponding prices in Column O and give a result.





--
Neall


"Jacob Skaria" wrote:

Neall

The earlier one returned the number of entries....Use the below to return
the sum

=SUMPRODUCT(--(YEAR(C8:C23)=2009),O8:O23)

If this post helps click Yes
---------------
Jacob Skaria


"Neall" wrote:

Thanks however, maybe I am missing something here

=SUM(IF(YEAR(C8:C23)=2009,1,0))

C8:c23 are the dates however there is no reference to the corresponding
pricing columns (O9:O28) to get the prices to add.

Basically if C9,C11,C15 = 2007 goto O9,11,15 and give me the sum.

Did I miss something?



--
Neall


"Jacob Skaria" wrote:

If you mean date in Column C use the below for number of parts purchased in
2009

=SUM(IF(YEAR(C8:C23)=2009,1,0))

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"Neall" wrote:

I have part prices (O8 - O23) and years they were purchased (C8 - C23) I want
to create a formula that adds up only parts purchased for X year.

Any suggestions

Thanks in advance
--
Neall