Need some help!

[Pingman]

I'm struggling and need help to do the following:


I'm pulling text, which can vary depending on the original result, from one sheet to another. This will show in cells E5:E24 and F5:F24 (not merged).

For example, after one review E5 could say "Building" and F5 could say "Post 01.06.2004". After the second review E6 could say "Building" and F6 could say "03-04". D5:D24 will show either "Pass", "Near Miss" or "Fail" for each review.

I want to summarise the number of different possibilities, as well as count the number of occurrences of "Pass", "Near Miss" or "Fail" in summary boxes for each combination.

In the first summary table I want the heading (merged cells I4:L4) to be the concatenate of E5 & F5, IF E5 says "Building" and F5 says "Post 01.06.2004" so that it displays "Building - Post 01.06.2004".

If that's true, then I want to count the number of occurrences of "Pass", "Near Miss" or "Fail" from D5:D24.

My next summary box will then summarise the "Building" and "03-04" combination and the number of occurrences of "Pass", "Near Miss" or "Fail" from D5:D24 but I can't get the formulas to work for the first box to allow me to then modify it for the various combinatons.

Can anyone help?

 

[etaf]

to concatenate
=E5&" - "&F5

to count you can use a countifs()
i'm assuming
"Building - Post 01.06.2004" can appear in rows 5 to 24 multiple times

if not , not sure what you are counting

why use merged calls I to L
usually merged cells cause issues with formulas