Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count total month per year | Excel Discussion (Misc queries) | |||
Count formel. Dont like the new year | Excel Worksheet Functions | |||
How to count by monday, tuesday, ...in a year? | Excel Discussion (Misc queries) | |||
Count returns for a period - year | Excel Worksheet Functions | |||
Get count of records for a particular month and year | Excel Discussion (Misc queries) |