Search code examples
excelexcel-formulaexcel-2010

Sumif months > specified date

Dataframe

 name subscribe_date 1/2019 2/2019  3/2019  .......  1/2021 2/2021 3/2021
2 Andy   2020-10-01      50     30     20       .......    200    40      60

I would like to create a post_subs_total column that takes the sum of row 2 from the cells 1/2021 to 3/2021 for the months after the subscribe date.

In the example above, the result would be:

 name subscribe_date 1/2020 2/2020  3/2020  .......  1/2021 2/2021 3/2021 post_subs_total
2 Andy   2020-10-01      50     30     20       .......    200    40      60        300

The data has to stay in horizontal format, because I would like to know how much each customer spends after his subscribe date.

enter image description here

Solution

  • First make sure that the header row containing your dates are truly Excel date values. You can have them formatted to look exactly the same (e.g. "3/2019") by formatting those cells using the custom date format of "m/yyyy", or any other format you choose.

    To generate the "post_subs_total" for the image of your worksheet, use this formula in Cell R2:

    =SUMIFS(C2:Q2,C1:Q1,">="&B2)