SumProduct Question

[Gina]

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

 

[Gina]

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

 

[M Kan]

=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.

 

[Gina]

=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.

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

 

[M Kan]

Thanks for the feedback, glad I could help out.