CODEWITHSUNDEEP
excelvbaexcel-formula
SDiab
SDiab

Reputation: 41

How to write a formula that skip other criteria`s and apply only the chosen values from the drop down list?

I have 2 sheets in excel file with two tables:

Sheet 1: Table 1 having columns [A,B,C,D]

Comment: This table is a filtered table containing multiple rows having multiple values I can filter directly from the header drop down to get the specific row related to the criteria I choose in the headers.

Sheet 2: Table 2 having Columns [A,B,C,D] and Cell [A4]

Comment: This table is a Filter Summary table for each column contain a drop down list containing a list of values , the user can choose one in each column to filter the table 1 in a formula located inside the Cell A4. Cell A4 containing the formula that will calculate the number of rows in table 1, after applying the criteria chosen from the table 2.

Question If i want to filter by two column or three columns only and not by all the columns located inside the table 2, how to write a formula that skip other criteria`s and apply only the chosen values from the table 2?

Code: Cell A4 contains the following code: =COUNTIFS(QA_ITEMS!A:A,$A$4,QA_ITEMS!B:B,$B$4,QA_ITEMS!C:C,$C$4,QA_ITEMS!D:D,$D$4)

Attached for your reference. Adding-Multiple Criteria in a formula dynamically

Upvotes: 0

Views: 194

Answers (1)

Michal
Michal

Reputation: 5828

enter image description here

I used named ranges, you can also hardcode the ranges or use a table:

 =SUMPRODUCT(
 (IF(ISBLANK(F2),MATCH("*",phase,0),F2=phase))*
 (IF(ISBLANK(G2),MATCH("*",sprint,0),G2=sprint))*
 (IF(ISBLANK(H2),MATCH("*",Item,0),H2=Item))*
 (IF(ISBLANK(I2),MATCH("*",type,0),I2=type)))

Cheers, Michal

Upvotes: 1

Related Questions