| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional sum equals 0
The following formula returns an answer of zero. However, if I sum via auto
filter, I get a sum. =SUM(IF(DATA4<DATEVALUE("4/1/2008"),IF(DATA6="",IF(DATA6DATEVALUE("3/31/2008"),$E$11:$E$15942,0),0),0)) I have two date colums (DATA4 & DATA6) that I want conditional statments and to sum a quantity colum (E). Date colum DATA6 will have both - cells with dates and blank cells that need to be counted. Notif.date Notif.qty Completn date 06/29/2006 1 07/10/2006 1 10/09/2008 01/17/2007 1 10/10/2008 02/02/2007 1 08/01/2008 01/09/2008 1 06/06/2008 01/09/2008 1 07/24/2008 01/21/2008 1 01/22/2008 1 10/13/2008 Is there a way to make this work with a formula? Thanks, -- wayne |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional sum equals 0
The second IF condition should be < rather than =
Also since this is an array formula use CTRL-SHIFT-ENTER instead of ENTER after typing/pasting the formula... I believe that the size of $E$11:$E$15942 should also match the size of Data4 and Data6 ranges. "wma" wrote: The following formula returns an answer of zero. However, if I sum via auto filter, I get a sum. =SUM(IF(DATA4<DATEVALUE("4/1/2008"),IF(DATA6="",IF(DATA6DATEVALUE("3/31/2008"),$E$11:$E$15942,0),0),0)) I have two date colums (DATA4 & DATA6) that I want conditional statments and to sum a quantity colum (E). Date colum DATA6 will have both - cells with dates and blank cells that need to be counted. Notif.date Notif.qty Completn date 06/29/2006 1 07/10/2006 1 10/09/2008 01/17/2007 1 10/10/2008 02/02/2007 1 08/01/2008 01/09/2008 1 06/06/2008 01/09/2008 1 07/24/2008 01/21/2008 1 01/22/2008 1 10/13/2008 Is there a way to make this work with a formula? Thanks, -- wayne |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional sum equals 0
Try to sumproduct formula:
=SUMPRODUCT(--($A$1:$A$5<"4/1/2008"),--($B$1:$B$5"3/31/2008"),$E$1:$C$6) *where A1:A5 is the range of your Data4 *where B1:B5 is Data6 (if this works, please click on the "Yes" on the Was this post helpful to you?" "wma" wrote: The following formula returns an answer of zero. However, if I sum via auto filter, I get a sum. =SUM(IF(DATA4<DATEVALUE("4/1/2008"),IF(DATA6="",IF(DATA6DATEVALUE("3/31/2008"),$E$11:$E$15942,0),0),0)) I have two date colums (DATA4 & DATA6) that I want conditional statments and to sum a quantity colum (E). Date colum DATA6 will have both - cells with dates and blank cells that need to be counted. Notif.date Notif.qty Completn date 06/29/2006 1 07/10/2006 1 10/09/2008 01/17/2007 1 10/10/2008 02/02/2007 1 08/01/2008 01/09/2008 1 06/06/2008 01/09/2008 1 07/24/2008 01/21/2008 1 01/22/2008 1 10/13/2008 Is there a way to make this work with a formula? Thanks, -- wayne |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional sum equals 0
Thanks, the < did turn my zero into 712. However, using the auto filter
method I get a quantity of 762. Appears like the formula is not considering the empty cells as I get 712 if I leave off the empty cell condition in auto filter and 762 if I use it. And yes, the'E' and DAT ranges are the same size. -- wayne "Sheeloo" wrote: The second IF condition should be < rather than = Also since this is an array formula use CTRL-SHIFT-ENTER instead of ENTER after typing/pasting the formula... I believe that the size of $E$11:$E$15942 should also match the size of Data4 and Data6 ranges. "wma" wrote: The following formula returns an answer of zero. However, if I sum via auto filter, I get a sum. =SUM(IF(DATA4<DATEVALUE("4/1/2008"),IF(DATA6="",IF(DATA6DATEVALUE("3/31/2008"),$E$11:$E$15942,0),0),0)) I have two date colums (DATA4 & DATA6) that I want conditional statments and to sum a quantity colum (E). Date colum DATA6 will have both - cells with dates and blank cells that need to be counted. Notif.date Notif.qty Completn date 06/29/2006 1 07/10/2006 1 10/09/2008 01/17/2007 1 10/10/2008 02/02/2007 1 08/01/2008 01/09/2008 1 06/06/2008 01/09/2008 1 07/24/2008 01/21/2008 1 01/22/2008 1 10/13/2008 Is there a way to make this work with a formula? Thanks, -- wayne |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional sum equals 0
I get 0.
=SUMPRODUCT(($D$11:$D$15942<"4/1/2008")*($G$11:$G$15942"3/31/2008")*($G$11:$G$15942<""),$E$11:$E$15942) -- wayne "Storm" wrote: Try to sumproduct formula: =SUMPRODUCT(--($A$1:$A$5<"4/1/2008"),--($B$1:$B$5"3/31/2008"),$E$1:$C$6) *where A1:A5 is the range of your Data4 *where B1:B5 is Data6 (if this works, please click on the "Yes" on the Was this post helpful to you?" "wma" wrote: The following formula returns an answer of zero. However, if I sum via auto filter, I get a sum. =SUM(IF(DATA4<DATEVALUE("4/1/2008"),IF(DATA6="",IF(DATA6DATEVALUE("3/31/2008"),$E$11:$E$15942,0),0),0)) I have two date colums (DATA4 & DATA6) that I want conditional statments and to sum a quantity colum (E). Date colum DATA6 will have both - cells with dates and blank cells that need to be counted. Notif.date Notif.qty Completn date 06/29/2006 1 07/10/2006 1 10/09/2008 01/17/2007 1 10/10/2008 02/02/2007 1 08/01/2008 01/09/2008 1 06/06/2008 01/09/2008 1 07/24/2008 01/21/2008 1 01/22/2008 1 10/13/2008 Is there a way to make this work with a formula? Thanks, -- wayne |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Display conditional formatting if cell value equals an integer | Excel Worksheet Functions | |||
| If a cell equals _, at the next row that equals _, return value fr | Excel Worksheet Functions | |||
| Index if conditional sum equals a value. | Excel Worksheet Functions | |||
| if a:a (range) equals january and c:c equals gas then add g:g ($) | Excel Worksheet Functions | |||
| custom filter does not work when selecting 'equals' X AND 'equals' | Excel Discussion (Misc queries) | |||
Linear Mode
