Multiple column/row data lookup-countif

[richiml]

Hello everyone,

My target is to generate the data filled in yellow.
Basically: there are certain tests to be performed at certain dates (Table 2), and there are features that will take the results of the tests only at specified dates (Table 3), and a table saying which test is relevant to what feature (Table 1).

At the end, I want to have a table that tells me how many tests that are relevant to a feature, were passed AND failed on the specific dates for each feature from Table 3.

I believe COUNTIFS, INDEX and MATCH might help me do this (along with some CONCATENATE to make the "1 passed, 3 failed" text), but I cannot make it work.

Many thanks in advance!

 

[richiml]

Here the data:

Table 1

feature 1

feature 2

feature 3

feature 4

Table 2

date A

date B

date C

Feature 1

Date A, Date B

Vibration

x

x

x

Vibration

passed

failed

failed

Feature 2

Date A, Date B, Date C

Sound

x

x

x

Sound

failed

failed

passed

Feature 3

Date C

Heating

x

Heating

passed

passed

passed

Feature 4

Date B, Date C

Communication

x

x

x

Communication

passed

failed

failed

Dates of the tests that are relevant to each feature (i.e. for "Feature 1", only tests ocurring in dates A & B will be considered)

x means that test will be performed for that feature: (i.e. ALL tests will be performed for "feature 1", but only "Sound" test will be performed for "Feature 2")

Dates on which each test was performed, independently of the features, and the outcome of the test

Table 4

Feature 1

Feature 2

Feature 3

Feature 4

date A

3 passed, 1 failed

0 passed, 1 failed

date B

1 passed, 3 failed

0 passed, 1 failed

0 passed, 3 failed

date C

1 passed, 0 failed

0 passed, 2 failed

1 passed, 2 failed