Adding up data that corresponds to a particular range of inputted dates using LOOKUP

I have a sheet with a column of dates (ColumnA), and next to it is a second column with data that corresponds to those dates (ColumnB). I have a field for a user to type in a start date (FieldA) and a field for a user to type in an end date (FieldB) and i want it to add up all the data in ColumnB that corresponds to the range of dates from FieldA to FieldB in Cell1. I know how to use LOOKUP to find the data that corresponds to FieldA and the data that corresponds to FieldB, but how do I make it sum up all the data for all the dates in between as well? This was my best guess for code for Cell1:

=SUM(LOOKUP(FieldA, ColumnA, ColumnB):LOOKUP(FieldB, ColumnA, ColumnB))

But that doesn't work. I want to add up all the data that corresponds to an entered date range, and I'm missing something. Thanks!

Hi Petalred, perhaps:

=SUMPRODUCT((A1:A50>=FieldA)*(A1:A50<=FieldB)*(B1:B50))

where FieldA and FieldB are cell references.

If FieldA has a start date, and FieldB has an end date (creating a "range" of dates) then my SUMPRODUCT formula should work just fine. Change your ranges to suit your actual data.

If it does not work, what result are you getting? It may be easiest just to upload a sample workbook with what you have in mind, and your expected results manually entered.