Search Formula ?

[safir4]

Hi All,

I'm not a excel expert and was hoping someone could help me with below query.

In the attached spreadsheet (Test Hy.elsx) their are number of columns that list the type of defects, and if there is a defect the cell will say "Yes", or "No". Column L counts the number of "yes's", which refer to defects found, and column M counts the number of "No's" which refers no defects found.

I want coulmn "N" to look at all the yes's for example in row 2 and work out, which are critical defects. So I want the formula to first of all count the number of defect's, then look in sheet "Type" were I have listed if the defect's are critical or not, and based on the search result provide a figure on the number of critical defects.

**The tab sheet "Type" lists the type of defects, which are exact same listed within the "Test" sheet and then in column B said if the defect is critical.

I hope I have explained clearly, if not please let me know.

Test Hy.xlsx

[safir4]

I would really appreciate if someone could with the above query.

[amphinomos]

Why not rearrange the data and use a pivot table ?

[safir4]

The pivot table would tell me how many defect's there are for each row, put it won't figure out which are critical ones. That's why I want the formula to add up the number of defect's, and then refer to the "type" sheet and determine which out the defect's is critical.

I hope I have made sense, or is there an easier way if so. Can somone show me.

[amphinomos]

Test Hy.xlsx

See if this won't do, I used the SUMPRODUCT formula. It displays the number of Yes's in your first column then displays the number of Yes's in the other column and only counts the rows where both columns show a Yes.

ps: You'll probably have to replace my semi-colons with commas in the formula as my excel uses semi-colons. Go figure...

[safir4]

Thank you very much (amphinomos), really appreicate the help.

[Teethless mama]

Try this:

N2: =SUM((IF(A2:K2="Yes",A$1:K$1)=Type!A$4:A$14)*(Type!B$4:B$14="Yes"))

Array formula: Press Ctrl+Shift+Enter, not just Enter
copy down