Revisions to Solve errors in a series of FILTER functions within array brackets?

added 16 characters in body; edited tags

Source Link

edited Jul 1, 2019 at 18:24

27.1k
10
53
78

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

Each FILTER'sFILTER'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 FILTERsFILTER formulas does not filter ANY results that equal B5 (or 1), the entire array function fails. How can I resolve this?

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.

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 FILTERs does not filter ANY results that equal B5 (or 1), the entire array function fails. How can I resolve this?

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.

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?

added 18 characters in body

Source Link

edited Jul 1, 2019 at 18:04

user0

27.1k
10
53
78

My only problem is, what if I don't have any values in one of the categories that correspondscorrespond 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")}={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 FILTERs does not filter ANY results that equal B5 (or 1), the entire array function fails. How can I resolve this?

My only problem is, what if I don't have any values in one of the categories that corresponds 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 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 FILTERs does not filter ANY results that equal B5 (or 1), the entire array function fails. How can I resolve this?

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 FILTERs does not filter ANY results that equal B5 (or 1), the entire array function fails. How can I resolve this?