Countifs with index match

Hi Struggling to even think about the logic for this one.

I have two worksheets:

Worksheet 1 named: “Matter Spend ALL”
Worksheet 2 named: “RM Report Data”

There is one unique field that links the two worksheets named: “Short Matter Name” which sits in column B on both worksheets. When I say unique, the matter names will be the same (same spelling and same format) in both worksheets but worksheet 2 may contain a number of duplicate entries.

I need to find a formula that does the following:
1) For worksheet 1 it finds each “Short Matter Name” where the matter is “Open”. The open status is in column “K” in Worksheet 1 (“Matter Spend ALL”).
2) Uses the outputs from 1) to count the matters in Worksheet 2 (“RM Report Data”) where the following is also true:
a. The Practice Group (column Q in Worksheet 2("RM Report Data") matches the practice group in A2 to A8 in the table below.
b. The PO number (column M in worksheet 2 (“RM Report Data”)) has an irregularity i.e. it either is states “awaited”, “tbc”, “TBC”, “?” or is blank.
c. The formulae should then sum the volume of irregularities found for each Practice Group in column B in the table below.

I have created formulas below in B2 and C2 of the table below. The ranking formulae works but the formulae in column B produces the wrong results. I think this is because I need an index/match in there somewhere.

Unfortunately, a pivot table is not a solution to this nor is power BI as my stakeholders don’t have this.

Any help would be greatly appreciated.

P
SNIPJAN2019.PNG

I have uploaded a sample file as requested. The column numbers in my original post have therefore changed but I have included a detailed explanation of the desired result in the attached. Thanks for your help so far.

Paul

Hi All / Administrator - I am assuming there is no solution to this - should I just close the post or is there any further information I could provide to help?

Regards

P

Bo_Ry - Your solution works like a treat - thank you so much and apologies for the Gringot miscalculation!.

Thank you so much

P