Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home

Can you sum all the amounts that occur on certain dates?

Posted by Jeff on February 21, 2001 11:42 AM
I have a table that lists a bunch of investments. This table has 2 columns - the Date of the investment and the Amount.

What I want to do is have a designated cell report the total of all the investment amounts that occured on a specified date. Note: I do not want to filter the data to only show the investments occuring on a particular date - it must simply be the total amount for the date's investments provided in the one cell.

The requested date will always change and the table will constantly be growing.

Is there a way to do this? Any help is much appreciated!


Re: Can you sum all the amounts that occur on certain dates?

Posted by SJC on February 21, 2001 11:55 AM

You can use SUMIF for this. The syntax is =SUMIF(range,criteria,sum_range) where range is the range for your investment dates, criteria is the date for which you want to sum the investment amounts, and sum_range is the the range of all investment amounts. For more info, search Excel help on SUMIF.


Re: Can you sum all the amounts that occur on certain dates?

Posted by Aladin Akyurek on February 21, 2001 12:23 PM

Or, use an array-formula.

Assuming your dates in A1:A5, investments in B1:B5.
Type in C1 a specified date. Array-enter (d.i. hit control+shift+enter to enter) the following formula, say in D1:

=SUM(IF(A1:A5=C1,1,0)*B1:B5)

Aladin


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.