How best to use sumproduct instead of conditional sum:

G

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?
 
G

Gina

Gina said:
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.
 
G

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. :(
 
T

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)
 
G

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top