How to count a certain range for data while checking for corresponding data?

Hi

I'm sorry if my problem is already answered elsewhere in this forum under a different question, but I couldn't find anything that was similar.

How do I count the number of schools in a selected region that are active while also checking what type the individual school is?

The attached picture is showing what I need. The overview sheet (green) is summing each of the regions in the second sheet (yellow and pink).

B2 should count column ACTIVE (J2:J4) for instances of "Yes" AND then the corresponding column TYPE (I) for "P".

C3 should do the same thing but checking for "S" in the TYPE column and so forth downwards.

The users must be able to add and remove lines (=shcool entities) in the second sheet without breaking the dynamic reference (should be no problem as long as they right click and add/delete within the set areas I guess?)

I've tried the formula helper and various search results online, but none seem to do what I want.
Are we talking a combination of nested formulas or similar?


excel1.png

Yes! That works great after converting from english to norwegian (why can't regional versions of Excel just use english as code language? :P)

Just one more thing, if I need too add extra TYPE parameters to include in the count, what's the proper syntax to add those?

Let's say I have entries of E and U (they could be different variations of primary schools) in addition to P in the TYPE column and all instances of P or E or U (with the same primary argument shall be counted in the B2 cell.