1

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.

THE PROBLEM

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

  • 1
    If 'Cash Account'!A:A and T1 contains true dates you are defeating the purpose of numerical comparison by converting one into a text representation of a date. Remove the TEXT function. – Jeeped Jan 26 '15 at 18:42
  • @Jeeped You should flesh out that comment and post it as an answer. – Excellll Jan 26 '15 at 18:47
1

I suggest using SUMIFS - you can use this version:

=SUMIFS('Cash Account'!$G$7:$G$350,'Cash Account'!$C$7:$C$350,A4,Account'!$A$7:$A$350,">"&$T$1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.