Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Getting sum of values in another sheet while using condition
Hi, i'm looking to get a sum of values in a range of dates from one sheet and to have the sum in another sheet.
for example i define in the second sheet that the range of dates is 3/4/12 to 3/3/13 The function will lookup the dates range and will sum the values that appear in the 2nd coloum. anyone got any ideas? |
#2
|
|||
|
|||
for easier understanding, in the picture attached i want to scan only some of the dates range and to sum the 4th coloumn value
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting sum of values in another sheet while using condition
Hi,
Am Thu, 21 Mar 2013 13:00:51 +0000 schrieb ser_man: Hi, i'm looking to get a sum of values in a range of dates from one sheet and to have the sum in another sheet. for example i define in the second sheet that the range of dates is 3/4/12 to 3/3/13 The function will lookup the dates range and will sum the values that appear in the 2nd coloum. anyone got any ideas? try: =SUMPRODUCT(--(Sheet1!$A$1:$A$1000=DATE(2012,4,3)),--(Sheet1!$A$1:$A$1000<=DATE(2013,3,3)),Sheet1!$B$1: $B$1000) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#4
|
|||
|
|||
Thank you for your reply; it's been very helpful.
Is there any way that the date won't be defined in the formula, but instead it will be defined by another cell value (for example date(a5)), since the reference to the date is what caused the problem from the begining? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting sum of values in another sheet while using condition
Hi,
Am Sun, 24 Mar 2013 11:58:35 +0000 schrieb ser_man: Thank you for your reply; it's been very helpful. Is there any way that the date won't be defined in the formula, but instead it will be defined by another cell value (for example date(a5)), since the reference to the date is what caused the problem from the begining? start date in A5, end date in A6: =SUMPRODUCT(--(Sheet1!$A$1:$A$1000=A5),--(Sheet1!$A$1:$A$1000<=A6),Sheet1!$B$1:$B$1000) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting sum of values in another sheet while using condition
Let's assume
A1 = Start Date B1 = Finish Date =SUMPRODUCT(--(Sheet1!$A$2:$A$1000=$A$1),--(Sheet1!$A$2:$A$1000<=$B$1),Sheet1!$B$2:$B$1000)) HTH Mick. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting sum of values in another sheet while using condition
On 25/03/2013 12:56 AM, Living the Dream wrote:
Let's assume A1 = Start Date B1 = Finish Date =SUMPRODUCT(--(Sheet1!$A$2:$A$1000=$A$1),--(Sheet1!$A$2:$A$1000<=$B$1),Sheet1!$B$2:$B$1000)) HTH Mick. What Claus said... :) Seems I included and extra, unwanted closing bracket, der..... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy from one Sheet and paste on another sheet based on condition | Excel Discussion (Misc queries) | |||
SUM OF NUM VALUES WITH CONDITION | Excel Discussion (Misc queries) | |||
If Then condition on Values | Excel Programming | |||
copy values generated by conditional formula in one sheet to the other work sheet as values | Excel Worksheet Functions | |||
copy values generated by conditional formula in one sheet to the other work sheet as values | Excel Discussion (Misc queries) |