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

  • Thread starter Thread starter Simple Simon
  • Start date Start date
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?
 
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

Calculate Payment Days and Date 3
Macro to get Amount and Date 6
Help with Excel sheets 3
Calculation of Interest / How to find Monthly Payment w/ Offset First Payment More options 2
Auto Copy Data from one worksheet to another 1
Year to Date Cummulative Totals 2
Gather Data from Multiple Sheets 1
Excel How to create a Accounts Receivable to calculate days in which payments against invoice are received 0

Back
Top