CODEWITHSUNDEEP
excel
GuiRJ
GuiRJ

Reputation: 3

Excel: SUM multiple lines by YEAR

I need a formula to sum the amount of dividends payed per year.

Column A shows the date and column B shows the amount payed.

Example of my data: https://i.sstatic.net/layJy.png

I've tried IF with SUM, SUMIF, VLOOKUP, etc., but no success.

Thanks.

Upvotes: 0

Views: 72

Answers (2)

Tsiry Rakotonirina
Tsiry Rakotonirina

Reputation: 731

Looking at your need, it seems that Pivot Table is your Solution instead of using if, Sum or anything else.

You can watch this example from Efficiency 365: Pivot Table Date Grouping Tutorial: How to group on year, month, quarter, day, or hour

Here is your end result:

Pivot Table Example

Upvotes: 1

Evil Blue Monkey
Evil Blue Monkey

Reputation: 2819

I guess the dividend per year is in column C and the first cell is C2. In such case, try this macro:

Sub DividendPerYearFormula()

    Range("C2").FormulaR1C1 = "=IF(YEAR(R[-1]C[-2])=YEAR(RC[-2]),"""",SUMIFS(C[-1],C[-2],""<""&DATE(YEAR(RC[-2]),12,31),C[-2],"">""&DATE(YEAR(RC[-2]),1,1)))"

End Sub

The formula itself is:

=IF(YEAR(A1)=YEAR(A2),"",SUMIFS(B:B,A:A,""<""&DATE(YEAR(A2),12,31),A:A,"">""&DATE(YEAR(A2),1,1)))

It will show you the value once per year. It is possible to have the values repeated in each row too. Of course a pivot table is also a good option.

Upvotes: 0

Related Questions