Changeable sum value

[Helen]

Hello,
Was hoping someone could help me, Im looking to be able
to make a sheet similar to this, where u enter a start
Date and a finish date and it will sum the values for
you, rather than having to do it manually everytime.
I've currently been trying using a countif and lookup
formula combination but with limited success, is there an
easier way to do this?

Thank you.

Helen

Start Date: 01-Feb-04
End Date: 01-Nov-04


01-Jan-04 0.00 0.00 0.00
01-Feb-04 0.00 1.85 0.00
01-Mar-04 13.50 0.00 0.00
01-Apr-04 0.00 0.00 0.00
01-May-04 0.00 5.20 0.00
01-Jun-04 0.00 0.00 0.00
01-Jul-04 0.00 0.00 0.00
01-Aug-04 0.58 0.32 0.60
01-Sep-04 0.00 0.00 0.00
01-Oct-04 3.60 0.00 0.00
01-Nov-04 0.00 0.00 0.00
01-Dec-04 0.00 0.25 0.00


=COUNTIF(B:B,"(LOOKUP(L14,A:A,B:B),LOOKUP(L16,A:A,B:B)")

 

[Guest]

If i understand you correctly
=SUMPRODUCT((A5:A65000>=D1)*(A5:A65000<=D2)*b5:b65000)
=SUMPRODUCT((A5:A65000>=D1)*(A5:A65000<=D2)*C5:C65000)
=SUMPRODUCT((A5:A65000>=D1)*(A5:A65000<=D2)*d5:d65000)

Where d1 is start date
and d2 is end date

 

[Don Guillett]

I would use sumproduct where b1 is start date and b2 is stop date
=sumproduct((a2:a200>=b1)*(a2:a200<b2)*b2:b200)

 

[Guest]

That is exactly what I am after thank you very much.

 

[Aladin Akyurek]

Since your data is sorted in ascending order on dates, you can set up a more
efficient/fast formula for summing etc.

=SUM(INDEX($B:$B,MATCH(G2,$A:$A)+(LOOKUP(G2,$A:$A)<>G2)):INDEX($B:$B,MATCH(H
2,$A:$A,1)))

where G2 houses a start date and H2 an end date.