INDEX, MATCH type COUNTIF/SUMIF issue.

[MrKris]

Hi everyone,

Last time I reached out to you, I felt the love with so many responses so quickly.

I am a teacher and I’m trying to look for ways to streamline my duties.

I'm currently reviewing the courses I teach - focussing on equipment used, and duration for each module.
I have been asked to report the number of reviews I have carried out on a course by course basis (however my courses consist of some common modules).
Essentially I'm trying to write a formula in R4 that will take the modules which are in Course A, and count if the corresponding row has an x (indicating I need to conduct a review of the module).

There are 3 possible equipment checks, and 3 duration checks which may be completed (Column I-K and Column L-N).
Each course is made up of a selection of standardised modules as indicated by the course matrix. The formula in Q2 works, and might be a clue how to solve this problem. In Q2 we have a SUMIF, but in R2 we need to COUNTIF. Simply changing it doesn't work, please help.
I have shown the stages of the calculation on the next tab - displaying progression.

Here’s a sample version of the type of data I’m working with. Please help!

Cheers,
MrKris

 

[Fluff]

How about
=SUMPRODUCT((INDEX($D$3:$H$10,,MATCH(P2,$D$2:$H$2,0))="x")*($I$3:$K$10="x"))