'Google Sheets: ArrayFormula sumifs, but return some symbol or empty cell if there's nothing to sum
I have the following table:
`Jan` `Feb` `Mar`
`P7` `Q7` `R7`
56.80 0 0
`Column E Column I Column N`
17 expense Jan-5 15.87
18 $ Jan-9 56.80
19 expense Feb-8 38.12
20 expense Mar 5 45.38
21 $ Mar-12 0.00
So I have
`Cell P7` with the following formula `=ArrayFormula(sumifs(N17:N,E17:E,"$",MONTH(I17:I),1))`
and
`Cell Q7` with the following formula `=ArrayFormula(sumifs(N17:N,E17:E,"$",MONTH(I17:I),2))`
and
`Cell R7` with the following formula `=ArrayFormula(sumifs(N17:N,E17:E,"$",MONTH(I17:I),3))`
that checks `Column I` for January dates (for `P7`), February dates (for `Q7`) and March dates (for `R7`), then checks if there's at least one sign `$` in corresponding `Column E` for those dates, and if there is, sums all corresponding amounts in `Column N`.
Now, my problem is this: the formula, as it is now, returns `0` even if there are no signs `$` for a specific month range, like for February in the above table. There are no `$` sign in `Column E` for February, yet cell `Q7` shows `0`.
I would like it to:
- return nothing at all (empty cell instead of `0`) if there are no amounts in `Column N` for a specific month marked with `$` sign in `Column E`
- return `0` only if there is `0.00` amount in `Column N` along with the `$` sign in corresponding `Column E`.
or
- return something else, like `~`, if there's nothing to sum up.
Let's say for March I could put `$` in `E21` and `~` in `N21` and see that `~` returned in `R7` if there are no more amounts to sum up.
Is there a way to do it?
Solution 1:[1]
Thanks to MAttKing's reference, simply add your formula in IFERROR(1/(1/ ComplexFunction() )) which should look something like this.
=IFERROR(1/(1/ArrayFormula(sumifs(D3:D,A3:A,"$",MONTH(B3:B),2))))
Output:
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |