Help required on filter function

[anishms]

In the attached excel file, I have written a formula with IFS function to check the criteria and take the count based on the criteria in cell E6:G6. I also have written a formula with filter function in cell E19:G19. But if I keep the selection blank in column B2 & B3, the filter function returns an error and the same thing happens for no selection in cell B2.
also the CALC# error is counted as 1.
Thanks in advance for your help!

 

[Peter Bartholomew]

I have a version of your workbook that appears to be working OK.
The problem is that I tried various ideas along the way, not all of which are pertinent to your question.
I used a LET function to get over the problem of #CALC! error being counted as one record.

= LET(
  Status_Filtered, FILTER(
     Opening_Status,
     ((Selection_Vertical="*")+(Vertical=Selection_Vertical))
     * ((Selection_IA="*")+(Original_Auditor=Selection_IA))
     * NOT(Opening_Status="Implemented & Verified")
     * NOT(Opening_Status="New")
     * (Risk_Rank=High_Risk)),
  IF(COUNTA(Status_Filtered)=1, 1-ISERROR(Status_Filtered), COUNTA(Status_Filtered) ) )

Without LET, the FILTER would always need to be carried out twice.

I think the error in your original FILTER was in the ISBLANK. You tested the data arrays rather than the scalar criterion value Selection_IA.

 

[anishms]

I have a version of your workbook that appears to be working OK.
The problem is that I tried various ideas along the way, not all of which are pertinent to your question.
I used a LET function to get over the problem of #CALC! error being counted as one record.

= LET(
  Status_Filtered, FILTER(
     Opening_Status,
     ((Selection_Vertical="*")+(Vertical=Selection_Vertical))
     * ((Selection_IA="*")+(Original_Auditor=Selection_IA))
     * NOT(Opening_Status="Implemented & Verified")
     * NOT(Opening_Status="New")
     * (Risk_Rank=High_Risk)),
  IF(COUNTA(Status_Filtered)=1, 1-ISERROR(Status_Filtered), COUNTA(Status_Filtered) ) )

Without LET, the FILTER would always need to be carried out twice.

I think the error in your original FILTER was in the ISBLANK. You tested the data arrays rather than the scalar criterion value Selection_IA.

I don't have LET function in my Excel 365

 

[Peter Bartholomew]

You can put the formulas for Status_Filtered_High etc. into a defined name to avoid having to write the whole lot three times in the final formula
= IF( COUNTA(Status_Filtered)=1, 1-ISERROR(Status_Filtered), COUNTA(Status_Filtered) )
Not as elegant!