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

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

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 |
+-------------------------------------------------------------------+

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