MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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!


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.

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