Forum Discussion

LauraFontys's avatar
LauraFontys
Copper Contributor
Mar 09, 2020

referencing a text cel when it clears certain conditions

I love using the SOMMEN.ALS function (sums.if) for my personal budget spreadsheet. 

But now I want to do something that I can't figure out how.

 

I have a tab that I use to track on which days my periodic expences are paid (rent, utilities, health insurence, etc.) I want to make a fomula so I can automatically see the date on which this expence has been paid when the expence name and the month match. 

So if colom B says 'rent' and colom C says 'March' on the same line, I want the cel to show the date noted in colom A. But I can't figure out how to do it. 

I have found online something about the formula's Index and Match, but I can't figure out how to construct them for my case

The normal ALS (IF) function doest work for what I want. who could help me?

excel
Formulas and Functions

5 Replies

  • Savia's avatar
    Savia
    Steel Contributor
    Mar 09, 2020
    This should be possible with a SUMIFS, e.g.:

    =SUMIFS(date column, expense type column, target expense, month name column, target month)

    Or, using some dummy cell references:

    =SUMIFS($A$8:$A$13,$B$8:$B$13,B8,$C$8:$C$13,C8)
    • LauraFontys's avatar
      LauraFontys
      Copper Contributor
      Mar 09, 2020

      Savia I have tried this, but it doesnt work. I have found online something about the formula's Index and Match, but I can't figure out how to construct them for my case

      • Savia's avatar
        Savia
        Steel Contributor
        Mar 09, 2020
        What do you mean by "doesn't work"? You get an error message, an incorrect value, nothing, or something else? I can probably troubleshoot it.

        INDEX / MATCH might work too if you create a helper column that combines the month and the expense type with =A1&B1. Then you could use:

        =INDEX(date column, MATCH(target month & target expense type, helper column, 0))