May 10 2020 11:56 AM
Hello all,
I'm hoping that this isn't too obvious but I've noticed this situation several times and have had to work around it.
I have a situation where the output of a FILTER function is creating a "0" (Zero) when the input cell is blank. I' tested for possible spaces or a real zero in the source and come up dry. The source really is a null.
The end result is when I go to compare the contents of the output cell in a SUMIFS there is a no-match situation and the values I need are not added into the sum.
As long as the source cell has anything in it, other than a null, all is good.
I've seen this before but have yet to find an answer.
This is the formula I'm using to create the data I need. The Cell in question is Transactions!Expense_Sub_Center. When if is Null or empty I get a zero-entry in the output cell.
=SORT((FILTER(CHOOSE({1,2,3,4,5,6},Transactions!Provider,Transactions!Expense_Center, Transactions!Expense_Sub_Center,Transactions!Account,Transactions!Due_Date,Transactions!Budgeted_Amount),(Transactions!Provider<>"")*(Transactions!Expense_Center<>"")*(Transactions!Category="Expense")*(Transactions!Budgeted_Amount<>""),"")),{1,2,3,4,5,6})
I've attached a copy of the file. The source sheet is "Transactions" and the output sheet is "Budget Table".
Thanks all,
TheOldPuterMan AKA John
Apr 01 2023 12:59 PM
May 25 2023 06:32 AM
Aug 09 2023 06:27 AM
Worked like a charm @Sylvie_in_France