Countif/Sumif should show empty value instead of 0 in arrayformula

1
votes

I have a lot of arrayformulas wrapped around countif and sumifs functions. I want the countifs/sumifs to return an empty value ("") instead of 0.

My workaround is very expensive and long:

=ARRAYFORMULA(IF(SUMIFS(Sheet!$C$1:$C; 'Sheet!$A$1:$A; ">="&$A3; Sheet!$A$1:$A; "<="&$B3)=0; ""; SUMIFS(Sheet!$C$1:$C; Sheet!$A$1:$A; ">="&$A3; Sheet!$A$1:$A; "<="&$B3)))

Here is an example sheet: https://docs.google.com/spreadsheets/d/1xyS0Y4gnG3zLyOGtycWytvTlA1NmqjEAy8H7QJatdSg/edit?usp=sharing

Is there a different way for this?

1
filtergoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
I have added an example - FlorianT.

1 Answers

1
votes
=IFERROR(SUM(QUERY(Sheet2!A:C; 
 "select C 
  where month(A)+1="&MONTH(A3)&" 
    and year(A)="&YEAR(A3); 0)))

0

=IFERROR(SUM(FILTER(Sheet2!C:C; 
 YEAR(Sheet2!A:A)=YEAR(A3); MONTH(Sheet2!A:A)=MONTH(A3))))

0