Copper Contributor

I am trying to add up the numbers in the cells of column j, based on the year designated in column k. I'm not sure what formula will do that. I've looked at IF statements, VLOOKUP and XLOOKUP. They return a specific piece of data. I want to sum the numbers in one column, that all have a match in the second column. This is a sample of the data:

 293.04 2019 447.36 2019 18,341.40 2019 313.20 2019 - 2019 313.20 2019 268.41 2019 52,205.76 2020 626.29 2020 - 2020 447.35 2020 1,342.08 2020 671.04 2020

So I want my results to say

2019 = \$19,976.61

2020 = \$55,292.52

what formula can I use to do this? Thanks!

4 Replies

# Re: need a suggestion please

Use SUMIF() or a pivot table.

# Re: need a suggestion please

ok, I'll try that. thank you!

# Re: need a suggestion please

If you have Excel 365

``````= LET(
distinctYears, UNIQUE(year),
annualTotals,  SUMIFS(amount, year, distinctYears),
HSTACK(distinctYears, annualTotals)
)``````

would return the result you specify.

thank you!