How to sum data from another sheet using date ranges?
 

I need to sum data from a colum using a specific date range from another
excel sheet. Sheet 1 contains my summary page extracting the sum from sheet
2 by listing date ranges on sheet 1 to find corresponding numerical data to
be summed that fall in the date ranges. Any ideas where to start?

How to sum data from another sheet using date ranges?
 

Your post wants for a few more details....but here's a guess:

With Sheet1
A1: (start date)
A2: (end date)

And....on sheet2...
Col_A contains dates
Col_B contains account names
Col_C contains numbers

Try something like this:
On Sheet1...
C1: =SUMPRODUCT((Sheet2!A1:A100=A1)*(Sheet2!A1:A100<= A2)*(Sheet2!C1:C100))

Or...if you want to sum amounts for a certain account names in the date range
On Sheet1!A3: (the account name)
and the formula should be:
C1:
=SUMPRODUCT((Sheet2!A1:A100=A1)*(Sheet2!A1:A100<= A2)*(Sheet2!B1:B100=A3)*(Sheet2!C1:C100))

Adjust range references to suit your situation.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Heeder07" wrote:

I need to sum data from a colum using a specific date range from another
excel sheet. Sheet 1 contains my summary page extracting the sum from sheet
2 by listing date ranges on sheet 1 to find corresponding numerical data to
be summed that fall in the date ranges. Any ideas where to start?

How to sum data from another sheet using date ranges?
 

Ron,

Thank you for the feedback. Your post has me starting in the right
direction but is it possible for the range of cells to be a full column or
does it have to be cell range specific? Otherwise, your feedback is most
appreciated.

"Ron Coderre" wrote:

Your post wants for a few more details....but here's a guess:

With Sheet1
A1: (start date)
A2: (end date)

And....on sheet2...
Col_A contains dates
Col_B contains account names
Col_C contains numbers

Try something like this:
On Sheet1...
C1: =SUMPRODUCT((Sheet2!A1:A100=A1)*(Sheet2!A1:A100<= A2)*(Sheet2!C1:C100))

Or...if you want to sum amounts for a certain account names in the date range
On Sheet1!A3: (the account name)
and the formula should be:
C1:
=SUMPRODUCT((Sheet2!A1:A100=A1)*(Sheet2!A1:A100<= A2)*(Sheet2!B1:B100=A3)*(Sheet2!C1:C100))

Adjust range references to suit your situation.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Heeder07" wrote:

I need to sum data from a colum using a specific date range from another
excel sheet. Sheet 1 contains my summary page extracting the sum from sheet
2 by listing date ranges on sheet 1 to find corresponding numerical data to
be summed that fall in the date ranges. Any ideas where to start?

How to sum data from another sheet using date ranges?
 

Check out sumif

"Heeder07" wrote:

Ron,

Thank you for the feedback. Your post has me starting in the right
direction but is it possible for the range of cells to be a full column or
does it have to be cell range specific? Otherwise, your feedback is most
appreciated.

"Ron Coderre" wrote:

Your post wants for a few more details....but here's a guess:

With Sheet1
A1: (start date)
A2: (end date)

And....on sheet2...
Col_A contains dates
Col_B contains account names
Col_C contains numbers

Try something like this:
On Sheet1...
C1: =SUMPRODUCT((Sheet2!A1:A100=A1)*(Sheet2!A1:A100<= A2)*(Sheet2!C1:C100))

Or...if you want to sum amounts for a certain account names in the date range
On Sheet1!A3: (the account name)
and the formula should be:
C1:
=SUMPRODUCT((Sheet2!A1:A100=A1)*(Sheet2!A1:A100<= A2)*(Sheet2!B1:B100=A3)*(Sheet2!C1:C100))

Adjust range references to suit your situation.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Heeder07" wrote:

I need to sum data from a colum using a specific date range from another
excel sheet. Sheet 1 contains my summary page extracting the sum from sheet
2 by listing date ranges on sheet 1 to find corresponding numerical data to
be summed that fall in the date ranges. Any ideas where to start?