Formula in one sheet to find first occurrence of date in another

S

Simple Simon

I have an Excel file that contains one spreadsheet containing dates
and payments, and a second statistical spreadsheet that contains
monthly totals. The totals are to be referenced by from and to dates
(i.e. start and end of each month), and I want a formula in the third
(total) cell that is the sum of all the payments appearing in the
first sheet between the from and to date.

Could someone please help?
 
P

papou

Hi
You could use SUMPRODUCT eg:
=SUMPRODUCT((Sheet1!C2:C500>=DATEVALUE("01/04/2008"))*(Sheet1!C2:C500<=DATEVALUE("30/04/2008"))*(Sheet1!B2:B500))

Where dates would be in range C2 to C500 Sheet1 and Amounts in range B2 to
B500 Sheet1

Please amend accordingly (especially date formats and ranges)

HTH
Cordially
Pascal
 

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

Similar Threads

External cell reference between two excel worksheets both in One Drive 0
Calculate Payment Days and Date 7
Help to show formula instead of value in a VBA code 1
YTD SUMIF based on current date 3
Update cell value based on name and specific date 0
Dsum and Dmax with structured references 1
Couple subs need help with 2
Gather Data from Multiple Sheets 1

Top