How best to use sumproduct instead of conditional sum:

[Gina]

This is the formula I am currently using:

=SUM(IF(Data!$I$2:$I$2000="Recordable",IF(Data!$A$2:$A$2500>=DATEVALUE("1/1/1995"),IF(Data!$A$2:$A$2500<DATEVALUE("1/1/1996"),Data!$L$2:$L$2000,0),0),0))

Basically I'm trying to find the total of recordable incidents that happened
within a date range, where the individual records are entered on a worksheet
called "Data".

What I'd like to be able to do is build this formula once, and then be able
to drag it across cells to calculate for other years as well. What I did was
make a new worksheet in the workbook called "List Values". Cell A2 has the
value 1/1/1995, A2 has 1/1/1996, etc.

Am I on the right track? Is there a way this could work?

=SUMPRODUCT(--(Data!$A$2:$A$2500>=ListValues!A2),--(Data!$A$2:$A$2500<=ListValues!A3),--(Data!$L$2:$L$2000,0)

Help?

 

[M Kan]

I think it would look like this:

=SUMPRODUCT(--(Data!$A$2:$A$2500>=ListValues!A2),--(Data!$A$2:$A$2500<=ListValues!A3),Data!$L$2:$L$2000)

Assuming column L is the data you want to summarize, you don't need the --
or the ,0 at the end

Here's my quick summary on SUMPRODUCT
http://www.kan.org/tips/excel_sumproduct_advanced1.php

 

[Gina]

This is the formula I am currently using:

=SUM(IF(Data!$I$2:$I$2000="Recordable",IF(Data!$A$2:$A$2500>=DATEVALUE("1/1/1995"),IF(Data!$A$2:$A$2500<DATEVALUE("1/1/1996"),Data!$L$2:$L$2000,0),0),0))

Basically I'm trying to find the total of recordable incidents that happened
within a date range, where the individual records are entered on a worksheet
called "Data".

What I'd like to be able to do is build this formula once, and then be able
to drag it across cells to calculate for other years as well. What I did was
make a new worksheet in the workbook called "List Values". Cell A2 has the
value 1/1/1995, A2 has 1/1/1996, etc.

Am I on the right track? Is there a way this could work?

=SUMPRODUCT(--(Data!$A$2:$A$2500>=ListValues!A2),--(Data!$A$2:$A$2500<=ListValues!A3),--(Data!$L$2:$L$2000,0)

Help?

I'm not getting it to work yet, but will read your sumproduct info and follow up in an hour or two.

 

[Teethless mama]

Ranges have to be equal

I think it would look like this:

=SUMPRODUCT(--(Data!$A$2:$A$2500>=ListValues!A2),--(Data!$A$2:$A$2500<=ListValues!A3),Data!$L$2:$L$2000)

Assuming column L is the data you want to summarize, you don't need the --
or the ,0 at the end

Here's my quick summary on SUMPRODUCT
http://www.kan.org/tips/excel_sumproduct_advanced1.php

 

[Gina]

IF(Data!$A$2:$A$2500>=ListValues!A2,IF(Data!$A$2:$A$2500<ListValues!A3,IF(Data!$I$2:$I$2500=$A$10,Data!$L$2:$L$2500,0),0),0)

This is how I have it working at the moment. I keep failing when trying to
convert to "SumProduct" format.

 

[Teethless mama]

=SUMPRODUCT(--(Data!$A$2:$A$2500>=ListValues!A2),--(Data!$A$2:$A$2500<=ListValues!A3),--(Data!$I$2:$I$2500=$A$10),Data!$L$2:$L$2500)

 

[Gina]

I think I've become so frustrated nothing is working. I'll try to give this
one a shot again in the morning, and I will give an update on how it goes. I
really appreciate the help.