Home |
Search |
Today's Posts |
#1
|
|||
|
|||
using filters with the sum function
Hi, I have 5 columns of data (please see attachment). What I would really like to do is to only add up the data in the 4 columns that are between 2 dates. For example, I would like to specify that all numbers in the 'calls made' column that are between 14/09/05 and 16/09/05 in the 'date changed' column are added up but leaving out all other data that does not fall between these two dates. I hope this is clear and I would be extremely grateful if someone could steer me in the right direction! Many many many thanks Justin +-------------------------------------------------------------------+ |Filename: screen1.gif | |Download: http://www.excelforum.com/attachment.php?postid=3806 | +-------------------------------------------------------------------+ -- justinelliott ------------------------------------------------------------------------ justinelliott's Profile: http://www.excelforum.com/member.php...o&userid=27176 View this thread: http://www.excelforum.com/showthread...hreadid=466817 |
#2
|
|||
|
|||
Here's one way:
A B C Result in 'C' column 1 Starting Date 9/14/2005 2 Ending Date 9/16/2005 3 4 Calls Made Date 5 5 9/13/2005 =if(and(b5=$b$1,b5<=$b$2),a5,0) 6 4 9/14/2005 7 2 9/15/2005 8 6 9/16/2005 9 7 9/17/2005 hope that helps. "justinelliott" wrote: Hi, I have 5 columns of data (please see attachment). What I would really like to do is to only add up the data in the 4 columns that are between 2 dates. For example, I would like to specify that all numbers in the 'calls made' column that are between 14/09/05 and 16/09/05 in the 'date changed' column are added up but leaving out all other data that does not fall between these two dates. I hope this is clear and I would be extremely grateful if someone could steer me in the right direction! Many many many thanks Justin +-------------------------------------------------------------------+ |Filename: screen1.gif | |Download: http://www.excelforum.com/attachment.php?postid=3806 | +-------------------------------------------------------------------+ -- justinelliott ------------------------------------------------------------------------ justinelliott's Profile: http://www.excelforum.com/member.php...o&userid=27176 View this thread: http://www.excelforum.com/showthread...hreadid=466817 |
#3
|
|||
|
|||
If you want to sum Column B where the corresponding date in Column A
equals Sepember 15, 2005, try... =SUMIF(A2:A10,"2005/09/15",B2:B10) If you want to sum Column B where the corresponding date in Column A is greater than or equal to 2005/09/14 and less than or equal to 2005/09/16, try... =SUMPRODUCT(--(A2:A10="2005/09/14"+0),--(A2:A10<="2005/09/16"+0),B2:B10) or =SUMPRODUCT(--(A2:A10=G2),--(A2:A10<=H2),B2:B10) ....where G2 contains your start date, and H2 contains your end date. Hope this helps! In article , justinelliott wrote: Hi, I have 5 columns of data (please see attachment). What I would really like to do is to only add up the data in the 4 columns that are between 2 dates. For example, I would like to specify that all numbers in the 'calls made' column that are between 14/09/05 and 16/09/05 in the 'date changed' column are added up but leaving out all other data that does not fall between these two dates. I hope this is clear and I would be extremely grateful if someone could steer me in the right direction! Many many many thanks Justin +-------------------------------------------------------------------+ |Filename: screen1.gif | |Download: http://www.excelforum.com/attachment.php?postid=3806 | +-------------------------------------------------------------------+ |
#4
|
|||
|
|||
Fantastic! Absolutely perfect - thanks! -- justinelliott ------------------------------------------------------------------------ justinelliott's Profile: http://www.excelforum.com/member.php...o&userid=27176 View this thread: http://www.excelforum.com/showthread...hreadid=466817 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
Function that filters a list (Database) for criteria in a range a. | Excel Worksheet Functions |