Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I add and IF function to a SUMIF function?
I need to sum values in a list that match other values and know that this can be done via the SUMIF function. However I also need to split the values based on a second numeric criteria. Does anyone know how this can be done. I thought about adding an IF function to the crtieria in the SUMIF. eg =SUMIF(A:A,B4&IF(A1DATE(2006,1,1),D:D) but can't get it to work. Any help would be greatly appreciated. Adam -- adscrim ------------------------------------------------------------------------ adscrim's Profile: http://www.excelforum.com/member.php...o&userid=30636 View this thread: http://www.excelforum.com/showthread...hreadid=502907 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I add and IF function to a SUMIF function?
For your tests, I think you need
=IF(A1DATE(2006,1,1),SUMIF(A:A,B4,D:D),0) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "adscrim" wrote in message ... I need to sum values in a list that match other values and know that this can be done via the SUMIF function. However I also need to split the values based on a second numeric criteria. Does anyone know how this can be done. I thought about adding an IF function to the crtieria in the SUMIF. eg =SUMIF(A:A,B4&IF(A1DATE(2006,1,1),D:D) but can't get it to work. Any help would be greatly appreciated. Adam -- adscrim ------------------------------------------------------------------------ adscrim's Profile: http://www.excelforum.com/member.php...o&userid=30636 View this thread: http://www.excelforum.com/showthread...hreadid=502907 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I add and IF function to a SUMIF function?
What if the date reference is in a range. Should have mentioned this in the original posts. on sheet 1 I have a list of names in column A and a SUMIF in column B. The SUMIF looks at column B on sheet 2 for the range, the name in column A on sheet 1 for the criteria and column C on sheet 2 for the sum range. Column A on sheet 2 has a list of dates which I want to incorporate into the SUMIF function so that the values in column C sheet2 are summed where the name in column b sheet2 equals the name picked from the list in A sheet 1 but only when the date in column A sheet 2 is above a certain value. Hope thats clear enough and thank for the early suggestion Bob -- adscrim ------------------------------------------------------------------------ adscrim's Profile: http://www.excelforum.com/member.php...o&userid=30636 View this thread: http://www.excelforum.com/showthread...hreadid=502907 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I add and IF function to a SUMIF function?
That was my original thought, until I read it properly <vbg
=SUMPRODUCT(--(Sheet2!$B$1:$B$1000=Sheet1!A1),--(Sheet2!A1:A100=DATE(2006,1 ,1)),Sheet2!C1:C1000) Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "adscrim" wrote in message ... What if the date reference is in a range. Should have mentioned this in the original posts. on sheet 1 I have a list of names in column A and a SUMIF in column B. The SUMIF looks at column B on sheet 2 for the range, the name in column A on sheet 1 for the criteria and column C on sheet 2 for the sum range. Column A on sheet 2 has a list of dates which I want to incorporate into the SUMIF function so that the values in column C sheet2 are summed where the name in column b sheet2 equals the name picked from the list in A sheet 1 but only when the date in column A sheet 2 is above a certain value. Hope thats clear enough and thank for the early suggestion Bob -- adscrim ------------------------------------------------------------------------ adscrim's Profile: http://www.excelforum.com/member.php...o&userid=30636 View this thread: http://www.excelforum.com/showthread...hreadid=502907 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I add and IF function to a SUMIF function?
thanks Bob the help is much appreciated -- adscrim ------------------------------------------------------------------------ adscrim's Profile: http://www.excelforum.com/member.php...o&userid=30636 View this thread: http://www.excelforum.com/showthread...hreadid=502907 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|