need a suggestion please

Question

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:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 293.042019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 447.362019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18,341.402019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 313.202019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 313.202019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 268.412019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 52,205.762020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 626.292020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 447.352020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,342.082020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 671.042020&nbsp;So I want my results to say2019 = $19,976.612020 = $55,292.52&nbsp;what formula can I use to do this? Thanks!

detlef_lewin · Answer

lstanderfer&nbsp;Use SUMIF() or a pivot table.&nbsp;

lstanderfer · Answer

ok, I'll try that. thank you!

peterbartholomew1 · Answer

lstanderfer&nbsp;If you have Excel 365= LET(
    distinctYears, UNIQUE(year),
    annualTotals,  SUMIFS(amount, year, distinctYears),
    HSTACK(distinctYears, annualTotals)
  )would return the result you specify.

lstanderfer · Answer

PeterBartholomew1&nbsp;thank you!

Forum Discussion

need a suggestion please

Share

Resources

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