Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding values for selected years
I am trying to add values from one column (H) if the date year in another (E) is 2006 or similar. I have tried the following with a 0 as the result (which is wrong). ={SUM(IF($E$7:$E$10070=(YEAR($E$7:$E$10070)=2006), $H$7:$H$10070))} Any ideas as to what is wrong? I suspect the =(YEAR($E$7:$E$10070)=2006 section of the formula. -- OSDavidL ------------------------------------------------------------------------ OSDavidL's Profile: http://www.excelforum.com/member.php...o&userid=31305 View this thread: http://www.excelforum.com/showthread...hreadid=509961 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding values for selected years
Hi David
One way =SUMPRODUCT(--(YEAR($E$7:$E$10070)=2006),$H$7:$H$10070) -- Regards Roger Govier "OSDavidL" wrote in message ... I am trying to add values from one column (H) if the date year in another (E) is 2006 or similar. I have tried the following with a 0 as the result (which is wrong). ={SUM(IF($E$7:$E$10070=(YEAR($E$7:$E$10070)=2006), $H$7:$H$10070))} Any ideas as to what is wrong? I suspect the =(YEAR($E$7:$E$10070)=2006 section of the formula. -- OSDavidL ------------------------------------------------------------------------ OSDavidL's Profile: http://www.excelforum.com/member.php...o&userid=31305 View this thread: http://www.excelforum.com/showthread...hreadid=509961 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding values for selected years
Hi Roger, Many thanks this works. Can I also add an AND function into the logical test? ie =SUMPRODUCT(AND(($B$7:$B$10070)=1770,--(YEAR($E$7:$E$10070)=2006)),$H$7:$H$10070) to look down another coloumn and verify if 2 items match before giving the answer? The above has a problem and doesn't want to work. Regards David -- OSDavidL ------------------------------------------------------------------------ OSDavidL's Profile: http://www.excelforum.com/member.php...o&userid=31305 View this thread: http://www.excelforum.com/showthread...hreadid=509961 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding values for selected years
Hi David
Yes you can, but not with that syntax. You don't need AND, you just have another set of tests. =SUMPRODUCT(--($B$7:$B$10070)=1770),--(YEAR($E$7:$E$10070)=2006),$H$7:$H$10070)The double unary minuses -- coerce the True's to 1's and False's to 0'sso they get multiplied together along with the values in column H.So1 x 1 x 100 will = 1000 x 1 x 100 will = 01 x 0 x 100 will = 0--RegardsRoger Govier"OSDavidL" wrotein message ... Hi Roger, Many thanks this works. Can I also add an AND function into the logical test? ie=SUMPRODUCT(AND(($B$7:$B$10070)=1770,--(YEAR($E$7:$E$10070)=2006)),$H$7:$H$10070) to look down another coloumn and verify if 2 items match before giving the answer? The above has a problem and doesn't want to work. Regards David -- OSDavidL ------------------------------------------------------------------------ OSDavidL's Profile:http://www.excelforum.com/member.php?action=getinfo&userid=31305 View this thread:http://www.excelforum.com/showthread.php?threadid=509961 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding values for selected years
Roger, Thanks, it looks Ok but I get a "formula contains an error" message with the last H10070 highlighted. Your logic looks good but I can't see why it does not work. Any suggestions? Rgs David -- OSDavidL ------------------------------------------------------------------------ OSDavidL's Profile: http://www.excelforum.com/member.php...o&userid=31305 View this thread: http://www.excelforum.com/showthread...hreadid=509961 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding values for selected years
Hi David
My apologies. Because I was copying and pasting from your formula with the AND in it, I inadvertently left a stray ")" in there after the B range It should be =SUMPRODUCT(--($B$7:$B$10070=1770), --(YEAR($E$7:$E$10070)=2006), $H$7:$H$10070) I have deliberately split the formula after the commas, to try to avoid any funny wraps in posting. -- Regards Roger Govier "OSDavidL" wrote in message ... Roger, Thanks, it looks Ok but I get a "formula contains an error" message with the last H10070 highlighted. Your logic looks good but I can't see why it does not work. Any suggestions? Rgs David -- OSDavidL ------------------------------------------------------------------------ OSDavidL's Profile: http://www.excelforum.com/member.php...o&userid=31305 View this thread: http://www.excelforum.com/showthread...hreadid=509961 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding values for selected years
Roger, Many thanks, all sorted ! David -- OSDavidL ------------------------------------------------------------------------ OSDavidL's Profile: http://www.excelforum.com/member.php...o&userid=31305 View this thread: http://www.excelforum.com/showthread...hreadid=509961 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Values from Formulas | Excel Discussion (Misc queries) | |||
How do I copy cell values (derived from formula), not references? | Excel Worksheet Functions | |||
Adding values from several worksheets - would appreciate the help | Excel Worksheet Functions | |||
Adding values from one worksheet to another | Excel Worksheet Functions | |||
Second serie doesn't use X-as values | Charts and Charting in Excel |