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:
=SUM(IF(A1:A5=C1,1,0)*B1:B5)
Aladin
