I run a small share investment club and of course use Excel to keep track of our activities. On one worksheet (Asset worksheet) I am trying (for each company we hold) to add up the dividends received for the past year (today-365) from values contained in another worksheet (Cash Account worksheet).
I am using Excel 2010 and an example of my Formula is:
=SUMPRODUCT(('Cash Account'!$C$7:$C$350=A4)*(TEXT('Cash Account'!$A$7:$A$350,"ddmmyyyy")>$T$1)*'Cash Account'!$G$7:$G$350)
WHAT I AM TRYING TO DO:
A4 contains Name of company (let's call it ACME).
$T$1 contains TODAY()-365.
Cash Account column A has Dates.
Cash Account column C has Company Name.
Cash Account column G has Amounts to be added up.
Therefore I am trying to look up all instances of ACME in the past year and adding up corresponding values.
I cannot get the date compare element to work (it might as well as not be there). I get a value returned that adds up all available values in column G (for ACME even those from more than 1 year ago.
Therefore as more rows get added to the Cash Account sheet, the only way to get this calculation to work is by manually (every month) amending the formula to only look at specific sets of rows (i.e. change the 7:350 range) to only include the rows covering Today-365.
I have tried all sorts of ways to amend this element of the formula. I have tried SUMIF and SUMIFS !! I have tried formatting all the cells that contain the dates.
SO - ALL HELP AND IDEAS APPRECIATED - MANY THANKS