0

enter image description here

I am trying to sum total a range of cells between date ranges ie column n has $ amounts column d has the transaction dates ie 1/3/2015 or 25/3/2015 or 25/4/2015 column b has the text saying drp or distribution - reinv

In another cell I am trying to sum or total (in column n) with the value of a range of different dates (column d) that contain different text (column b) ie cell n48 is 50, n65 is 85, n165 is 36

with the dates ie cell d48 is 1/3/2015, d65 is 25/3/2015 and d165 is 25/4/2015

with different text that says drp or distribution - reinv ie cell b48 is drp, b65 is distribution - reinv, b165 is drp

If I wanted to sum the amounts between 1/3/2015 to 31/3/2015 with drp then the total would be 50. Also if I wanted to sum the amounts between 1/4/2015 to 30/4/2015 with drp the sum total would be 36 If I wanted to sum the amounts between 1/3/2015 to 31/3/2015 with drp and distribution - reinv the sum would be 115

What would the formula be for these different questions

hope you can help, it has been driving me nuts and cant work it out

Improve this question
3
  • Hi Murray, welcome to SU. I think I can follow what you mean, but to make it easier, could you populate a small sheet with the values above and what you expect the result to be? Then screenshot that section of the spreadsheet and edit and add the image? It will make it clearer.
    – Paul
    Sep 7, 2017 at 5:59
  • Hi Murray, did you look into pivot tables? Then can easily do what you want, so you don't need any formulas.
    – Michthan
    Sep 7, 2017 at 6:01
  • No I havent, dont know about these
    – murray
    Sep 7, 2017 at 6:02

1 Answer 1

Reset to default
0

Like Michthan said, a pivot table can do this but than can be daunting to someone who's never worked with them before. However, the SUMIFS() function is exactly what you need in this case:

Scenario 1:

=SUMIFS($N:$N,$B:$B,"drp",$D:$D,">="&DATE(2015,3,1),$D:$D,"<="&DATE(2015,3,31))

Scenario 2:

=SUMIFS($N:$N,$B:$B,"drp",$D:$D,">="&DATE(2015,4,1),$D:$D,"<="&DATE(2015,4,30))

Scenario 3:

=SUM(SUMIFS($N:$N,$B:$B,{"drp","distribution - reinv"},$D:$D,">="&DATE(2015,3,1),$D:$D,"<="&DATE(2015,3,31)))

While these will work for exactly the conditions you stated (and you can edit the dates in the formulas however you like), they're not very flexible in case you wanted to change your criteria. It would be better to keep your criteria in some cells like $O$1:$O$4 and refer to those cells in the formula like so: 

$O$1 = drp [text]
$O$2 = 1/3/2015 [start date]
$O$3 = 31/3/2015 [end date]

And the formula would become

=SUMIFS($N:$N,$B:$B,$O$1,$D:$D,">="&$O$2,$D:$D,"<="&$O$3)

Side note: I couldn't get multiple OR() text criteria with cell references to work, even within an INDIRECT() function. If anyone can provide additional guidance, I'm sure Murray would appreciate it.

Improve this answer
1
  • Hi Ron Just would like to thank you so much for your help on this. I have put your formulas for each scenario into my spreadsheet and it worked perfectly. I can now calculate my investment portfolio income each month and it gives me an accurate figures. They surely are complicated and complex formulas, I would never have got that on my own. I will also look at the other option you have mentioned, as this might be easier to manage but for now I dont envisiage my text criteria condition to change. I greatly appreciate what you did and again thank you
    – murray
    Sep 8, 2017 at 4:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .