Hi Everyone,
Request your help in the following formula where I wanted to include 4 columns in the table namely Q1 to Q4 in the red highlighted portion.
IF(ISERROR(SUM(FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q1]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))),0,COUNTA((FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q1]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))))
currently I have added the formula 4 times referring to columns Q1 to Q4 in each set of the formula as given below- (refer column V14 of the attached file)
=IF(ISERROR(SUM(FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q1]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))),0,COUNTA((FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q1]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))))+
IF(ISERROR(SUM(FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q2]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))),0,COUNTA((FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q2]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))))+
IF(ISERROR(SUM(FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q3]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))),0,COUNTA((FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q3]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))))+
IF(ISERROR(SUM(FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q4]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))),0,COUNTA((FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q4]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))))
Any help on the above to simply the formula is greatly appreciated.
Request your help in the following formula where I wanted to include 4 columns in the table namely Q1 to Q4 in the red highlighted portion.
IF(ISERROR(SUM(FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q1]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))),0,COUNTA((FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q1]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))))
currently I have added the formula 4 times referring to columns Q1 to Q4 in each set of the formula as given below- (refer column V14 of the attached file)
=IF(ISERROR(SUM(FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q1]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))),0,COUNTA((FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q1]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))))+
IF(ISERROR(SUM(FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q2]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))),0,COUNTA((FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q2]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))))+
IF(ISERROR(SUM(FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q3]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))),0,COUNTA((FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q3]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))))+
IF(ISERROR(SUM(FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q4]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))),0,COUNTA((FILTER(Audit_Plan[Risk Rating],(Audit_Plan[Risk Rating]=U14)*(Audit_Plan[Q4]=CHAR(252))*((ISBLANK($U$3))+(Audit_Plan[Vertical]=$U$3))))))
Any help on the above to simply the formula is greatly appreciated.