# Conditional sum equals 0

W

#### wma

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(DATA6>DATEVALUE("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,

S

#### Sheeloo

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.

S

#### Storm

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

you?"

W

#### wma

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.

W

#### wma

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)