Forum Discussion
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 ...
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))
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))
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)