SumProduct Question
 

I am currently using a very cumbersome calculation to return a value in Cells
H3:H100 that is this:

=SUM(IF(Data!$A$2:$A$2500=DATEVALUE("1/1/2005"),IF(Data!$A$2:$A$2500<=DATEVALUE("2/1/2005"),IF(Data!$D$2:$D$2500<=1,Data!$F$2:$F$2500,0 ),0),0))

I built this formula using the conditional sum function in MS Excel.

I unfortunately have to use this type of formula over 500 times on this
particular sheet, and was hoping someone might be able to show me a better
way to get this value through using the sumproduct function.

Thank you!

Gina

SumProduct Question
 

I should also mention that I do have the start-end dates in the A (start
date) and B (end date) column in the same row as the calculation (column I)
on this worksheet. That means I could use those cells as reference instead
of DateValue if it would be easier?

Thanks,
Gina

SumProduct Question
 

=SUMPRODUCT(--(Data!$A$2:$A$2500=date_ref1),--(Data!$A$2:$A$2500<=date_ref2),--(Data!$D$2:$D$2500<=1),Data!$F$2:$F$2500)

If I understand correctly, sum the values of F, based on 3 criteria? I
think that will work. date_ref1 and 2 would be your dates.

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Gina" wrote:

I am currently using a very cumbersome calculation to return a value in Cells
H3:H100 that is this:

=SUM(IF(Data!$A$2:$A$2500=DATEVALUE("1/1/2005"),IF(Data!$A$2:$A$2500<=DATEVALUE("2/1/2005"),IF(Data!$D$2:$D$2500<=1,Data!$F$2:$F$2500,0 ),0),0))

I built this formula using the conditional sum function in MS Excel.

I unfortunately have to use this type of formula over 500 times on this
particular sheet, and was hoping someone might be able to show me a better
way to get this value through using the sumproduct function.

Thank you!

Gina

SumProduct Question
 

"M Kan" wrote:

=SUMPRODUCT(--(Data!$A$2:$A$2500=date_ref1),--(Data!$A$2:$A$2500<=date_ref2),--(Data!$D$2:$D$2500<=1),Data!$F$2:$F$2500)

If I understand correctly, sum the values of F, based on 3 criteria? I
think that will work. date_ref1 and 2 would be your dates.

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Gina" wrote:

I am currently using a very cumbersome calculation to return a value in Cells
H3:H100 that is this:

=SUM(IF(Data!$A$2:$A$2500=DATEVALUE("1/1/2005"),IF(Data!$A$2:$A$2500<=DATEVALUE("2/1/2005"),IF(Data!$D$2:$D$2500<=1,Data!$F$2:$F$2500,0 ),0),0))

I built this formula using the conditional sum function in MS Excel.

I unfortunately have to use this type of formula over 500 times on this
particular sheet, and was hoping someone might be able to show me a better
way to get this value through using the sumproduct function.

Thank you!

Gina


This was perfect. I put in the date references and it returned exactly the
value that I needed. Thank you so much.
Gina

SumProduct Question
 

Thanks for the feedback, glad I could help out.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Gina" wrote:



"M Kan" wrote:

=SUMPRODUCT(--(Data!$A$2:$A$2500=date_ref1),--(Data!$A$2:$A$2500<=date_ref2),--(Data!$D$2:$D$2500<=1),Data!$F$2:$F$2500)

If I understand correctly, sum the values of F, based on 3 criteria? I
think that will work. date_ref1 and 2 would be your dates.

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Gina" wrote:

I am currently using a very cumbersome calculation to return a value in Cells
H3:H100 that is this:

=SUM(IF(Data!$A$2:$A$2500=DATEVALUE("1/1/2005"),IF(Data!$A$2:$A$2500<=DATEVALUE("2/1/2005"),IF(Data!$D$2:$D$2500<=1,Data!$F$2:$F$2500,0 ),0),0))

I built this formula using the conditional sum function in MS Excel.

I unfortunately have to use this type of formula over 500 times on this
particular sheet, and was hoping someone might be able to show me a better
way to get this value through using the sumproduct function.

Thank you!

Gina


This was perfect. I put in the date references and it returned exactly the
value that I needed. Thank you so much.
Gina