Apart from conditional formatting, is there a way to filter by values in this example to find out th-CodePudding

I have a group of companies in this table, ranked based on 1 to 4, 1 being the lowest and 4 being the highest, on 4 different criteria. I intend to find the top companies that meet either 1, 2, 3 or all 4 criteria. Apart from conditional formatting, I am unsure on the best method to help filter the data to find the top companies. In addition, I am also unsure (apart from manually counting) to help visualize the data based on the number of companies that meet the criteria.

I hope that the above explanation is clear enough and am glad to provide any other info if needed.

Intended outcome: I hope to be able to visualise the top companies with at least 1 criteria met, 2 criteria met, 3 criteria met and all 4 criteria met in a table. I am not sure whether this is possible and am open to suggestions on how it may be done.

CodePudding user response：

Well, you need use a FILTER() Function to arrive at the expected outcome,

• Formula used in cell H2

=FILTER(B2:F19,(C2:C19>2.2)*(D2:D19>2.5)*(E2:E19>3.3)*(F2:F19>2.1))

OP asked, whether it is possible to show all the criteria, individually as well, then the same formula works for us as well,

• Formula used in cell H2

=FILTER(B2:C19,C2:C19>2.2)

• Formula used in cell K2

=FILTER(CHOOSE({1,2},B2:B19,D2:D19),D2:D19>2.5)

Or,

=FILTER(FILTER(B2:F19,D2:D19>2.5),{1,0,1,0,0})

• Formula used in cell N2

=FILTER(CHOOSE({1,2},B2:B19,E2:E19),E2:E19>3.3)

Or,

=FILTER(FILTER(B2:F19,E2:E19>3.3),{1,0,0,1,0})