Forum Discussion
Solved
How to sum based on a specific cell
I'm doing an excel for quarterly results where I need to add the results on a Year to Date basis. So let's say I have 1Q2020 2Q2020 3Q2020 4Q2020 1Q2021 2Q2021 3Q2021 4Q2021 3Q2021 ...
Let;s say the quarterly results are in A1:H2, and the cell in bold is J1. The YTD is returned by the formula
=SUMPRODUCT(A2:H2,(LEFT(A1:H1)<=LEFT(J1))*(RIGHT(A1:H1,4)=RIGHT(J1,4)))
Let;s say the quarterly results are in A1:H2, and the cell in bold is J1. The YTD is returned by the formula
=SUMPRODUCT(A2:H2,(LEFT(A1:H1)<=LEFT(J1))*(RIGHT(A1:H1,4)=RIGHT(J1,4)))
Wow, I had to make some adjustments for it to exactly fit my sheet but **bleep** it worked. I really thought my problem didn't have a solution. Thanks!