MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Function problem

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

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

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

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

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: