Google-sheets – Solve errors in a series of FILTER functions within array brackets

filterformulasgoogle sheetsgoogle-sheets-arrayformulasorting

I have been working on a budget project on Google Sheets. My goal is for a range of data to be filtered by date (which is represented as an integer between 1 and 31 for the day of the month). All entries in the first two columns are returned if the entry in the third column is equal to 1 (or whatever day of the month for which I am looking). And I require that on ~25 categories, and I want them to all be in one function, so I combined 25 filter functions in an array and they all combined nicely.

My only problem is, what if I don't have any values in one of the categories that correspond to that date. The FILTER function returns an error and then the ENTIRE array function fails. I tried using IFERROR to return a string, but the array function does not like having strings as an argument.

={iferror(sort(FILTER(Sheet1!$AH$13:$AI$62,Sheet1!$AJ$13:$AJ$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$AK$13:$AL$62,Sheet1!$AM$13:$AM$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$AN$13:$AO$62,Sheet1!$AP$13:$AP$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$AQ$13:$AR$62,Sheet1!$AS$13:$AS$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$AT$13:$AU$62,Sheet1!$AV$13:$AV$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$AW$13:$AX$62,Sheet1!$AY$13:$AY$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$AZ$13:$BA$62,Sheet1!$BB$13:$BB$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$BC$13:$BD$62,Sheet1!$BE$13:$BE$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$BF$13:$BG$62,Sheet1!$BH$13:$BH$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$BI$13:$BJ$62,Sheet1!$BK$13:$BK$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$BL$13:$BM$62,Sheet1!$BN$13:$BN$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$BO$13:$BP$62,Sheet1!$BQ$13:$BQ$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$BR$13:$BS$62,Sheet1!$BT$13:$BT$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$BU$13:$BV$62,Sheet1!$BW$13:$BW$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$BX$13:$BY$62,Sheet1!$BZ$13:$BZ$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$CA$13:$CB$62,Sheet1!$CC$13:$CC$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$CD$13:$CE$62,Sheet1!$CF$13:$CF$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$CG$13:$CH$62,Sheet1!$CI$13:$CI$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$CJ$13:$CK$62,Sheet1!$CL$13:$CL$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$CM$13:$CN$62,Sheet1!$CO$13:$CO$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$CP$13:$CQ$62,Sheet1!$CR$13:$CR$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$CS$13:$CT$62,Sheet1!$CU$13:$CU$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$CV$13:$CW$62,Sheet1!$CX$13:$CX$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$CY$13:$CZ$62,Sheet1!$DA$13:$DA$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$DB$13:$DC$62,Sheet1!$DD$13:$DD$62=B5),2,TRUE),"None Today");
iferror(sort(FILTER(Sheet1!$DE$13:$DF$62,Sheet1!$DG$13:$DG$62=B5),2,TRUE),"None Today")}

Each FILTER's first argument refers to two columns that contain the text I want to be returned based on the second argument as a condition (in this case equaling B5 or 1). The sort function organizes this alphabetically. However, if one of these FILTER formulas does not filter ANY results that equal B5 (or 1), the entire array function fails. How can I resolve this?

Data lies on Sheet1

The array is placed in B6 on Sheet2
Data Being Filtered
Filtered Data

Best Answer

...understanding the ARRAY_LITERAL ERROR:

  • until both queries/filters output something then all is good:

    0

  • however if one of those queries/filters doesn't have anything to output it outputs #N/A - No matches are found in QUERY/FILTER evaluation. - the issue is that #N/A is only in the 1st cell:

    e

  • but array expects that matrix on both sides to be same (4 columns from both queries/filters):

    0

  • so we wrap each query into IFERROR and in case of error we output fake row with 4 fake columns - {"","","",""} - which will trick the array to output it like:

    0

  • therefore try like this:

={IFERROR(SORT(FILTER(Sheet1!$AH$13:$AI$62,Sheet1!$AJ$13:$AJ$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$AK$13:$AL$62,Sheet1!$AM$13:$AM$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$AN$13:$AO$62,Sheet1!$AP$13:$AP$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$AQ$13:$AR$62,Sheet1!$AS$13:$AS$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$AT$13:$AU$62,Sheet1!$AV$13:$AV$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$AW$13:$AX$62,Sheet1!$AY$13:$AY$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$AZ$13:$BA$62,Sheet1!$BB$13:$BB$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$BC$13:$BD$62,Sheet1!$BE$13:$BE$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$BF$13:$BG$62,Sheet1!$BH$13:$BH$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$BI$13:$BJ$62,Sheet1!$BK$13:$BK$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$BL$13:$BM$62,Sheet1!$BN$13:$BN$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$BO$13:$BP$62,Sheet1!$BQ$13:$BQ$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$BR$13:$BS$62,Sheet1!$BT$13:$BT$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$BU$13:$BV$62,Sheet1!$BW$13:$BW$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$BX$13:$BY$62,Sheet1!$BZ$13:$BZ$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$CA$13:$CB$62,Sheet1!$CC$13:$CC$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$CD$13:$CE$62,Sheet1!$CF$13:$CF$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$CG$13:$CH$62,Sheet1!$CI$13:$CI$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$CJ$13:$CK$62,Sheet1!$CL$13:$CL$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$CM$13:$CN$62,Sheet1!$CO$13:$CO$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$CP$13:$CQ$62,Sheet1!$CR$13:$CR$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$CS$13:$CT$62,Sheet1!$CU$13:$CU$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$CV$13:$CW$62,Sheet1!$CX$13:$CX$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$CY$13:$CZ$62,Sheet1!$DA$13:$DA$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$DB$13:$DC$62,Sheet1!$DD$13:$DD$62=B5),2,1),{"None Today",""});
  IFERROR(SORT(FILTER(Sheet1!$DE$13:$DF$62,Sheet1!$DG$13:$DG$62=B5),2,1),{"None Today",""})}

Related Topic