Forum Discussion
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 YTD
10 20 30 40 15 25 35 45 ?
I already have a cell (bold letters) where I input which quarter I want to see, so I need that the YTD adds the numbers for that quarter and ONLY the previous quarters of that same year... in the example above, in bold, there's 3Q2021 so I need the YTD to add 15+25+35 once I input 3Q2021 on the bold cell.
I thought about a formula where based on the bold input it adds 1 cell below for 1Q, the cell below and the previous one for 2Q, the cell below and the two previous ones for 3Q, etc... But I couldn't figure a formula that lets you add above or below cells based on a fixed cell.
I thought something like the index+match formula but instead of giving out a certain cell above/below, I need this to ADD/SUM certain cells above/below, not just give out a single cell.
Any ideas? Thanks!
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)))
2 Replies
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)))
- FernandoC9Copper ContributorWow, 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!