Forum Discussion

FernandoC9's avatar
FernandoC9
Copper Contributor
May 23, 2022
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  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!

  • FernandoC9 

    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

  • FernandoC9 

    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)))

    • FernandoC9's avatar
      FernandoC9
      Copper Contributor
      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!

Resources