referencing a text cel when it clears certain conditions

Copper Contributor

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?

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

@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

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

@Savia 

it first gave me a value of 0-jan, which is nothing. But it turned out I messed somthing up. so it works now. 

 

Now when the formula can't find a line fulfilling all the criterum it gives the 0-jan response. is there a way to construct the formula to substitute that for an 'X'?

 

by the way, thank you so much for helping me. I really appriciate it. 

also, sorry for my crappy spelling and explanation. English is my 2nd language, and I have to translate the formula's from my native tongue to English, and that doesnt always go well.

No problem. 0-Jan is what 0 looks like with date formatting - which makes sense; if there are no rows matching your criteria then a sum of 0 is appropriate. You could use an IF to change how that is displayed:

=IF(your SUMIFS formula=0, "X", your SUMIFS formula)