Extracting occasion occurred..

[SirJB7]

@dawa
Hi!
You have to choose between 2 major paths to become a Ninja.
One, the most usual but long and hard is to learn, help and serve.
The other, the most unusual but short and easy is to solve this:
http://chandoo.org/forum/threads/sticky-outlining-who-the-ninjas-are.10826/#post-62882
Now it's up to you... Good luck!
Regards!

 

[dawa]

You are awesome... and as usual always helpful. Thank You ............ Luke

 

[dawa]

Thank you luke. If we have to extract the data from multiple sheet how do we do it as from sheet 1, sheet 2, sheet 3 and sheet 4.

 

[Luke M]

Hi dawa,
The easier way would be to construct the same formula on all the sheets, in the same cell. You can certainly make the reference part (where it looks up the name/id) refer just to one spot.
=SUMPRODUCT(((INDEX(D3:F33,,MATCH('My Sheet'!I12,D1:F1,0))="SL")+(INDEX(D3:F33,,MATCH('My Sheet'!I12,D1:F1,0))="UL"))*(((INDEX(D2:F32,,MATCH('My Sheet'!I12,D1:F1,0))<>"SL")+(INDEX(D2:F32,,MATCH('My Sheet'!I12,D1:F1,0))<>"UL"))=2))

Then, to get the grand total, use a 3D summation that drills through all the sheets. So, if the formula is in cell I20:
=SUM('My sheet:Sheet5'!I20)

 

[dawa]

Thank you so much luke.... help appreciated.