Can it be done?

[Cory Sanders]

Hello,

I have what I think is a unique question that I have been unable to find an answer to.
In the sample file, I have three sites I need to compile into a master list. The master table must count failed work order inspections by two different groups based on the appearance of the word fail at least once per work order. Also, there are two groups in the master but four job titles. I have simplified them to show what category they fall into.
I have tried many COUNTIFS formulas but have been unsuccessful. Excel 2010.

 

[Hui]

C5: =COUNTA(INDIRECT(Master!$B5&"!$A$7:$A$100"))

D5: =COUNTIFS(INDIRECT(B5&"!$B$7:$B$100"),"carpenter", INDIRECT(B5&"!$C$7:$C$100"),"fail")

E5: =COUNTIFS(INDIRECT(B5&"!$B$7:$B$100"),"electrician", INDIRECT(B5&"!$C$7:$C$100"),"fail")

Copy all 3 cells down
Adjust 100 to include all your future data

 

[Cory Sanders]

Thank you Mr. Hui for responding to my question. The formula is a great help!

 

[Im_Offset]

C5: =COUNTA(INDIRECT(Master!$B5&"!$A$7:$A$100"))

D5: =COUNTIFS(INDIRECT(B5&"!$B$7:$B$100"),"carpenter", INDIRECT(B5&"!$C$7:$C$100"),"fail")

E5: =COUNTIFS(INDIRECT(B5&"!$B$7:$B$100"),"electrician", INDIRECT(B5&"!$C$7:$C$100"),"fail")

Copy all 3 cells down
Adjust 100 to include all your future data

I have a sneaky suspicion that Hui gravitated to your post because of the title! He knows Excel can do anything, and when you question that belief, he sets out to quickly show it can.