• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help : FILTER formula > include argument

anishms

Member
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.
 

Attachments

Peter Bartholomew

Well-Known Member
I can't help but feel I have missed something here! My formula is
= SUMIFS(Audit_Plan[N], Audit_Plan[Risk Rating], Rating, Audit_Plan[Vertical], V)
I have introduced a helper column N to represent the number of audits to be conducted within a given year. If the answer is always 1, the field is redundant and COUNTIFS can be used in place of SUMIFS.
Another trick was to replace your Wingding check marks by 1 but then use number formatting to display the check mark in place of any positive number. That means to condition can be summed or used a Boolean by dependent formulae. A further feature of the formula is that it returns results for the High/Medium/Low ratings as a spilt array.
 

Attachments

Top