Using the result of a Filter formula to identify cells in a table?

[unclefish]

Dear Excelists,

I'd appreciate your comments regarding the following problem.

• I have two separate tables: the first table listing patients (Patient #), the region where they received a treatment (Region) and the treatment option (Treatment). The second table summarizes the patients (Patient #), the treatment they received (Treatment) and the gender of the patients (Gender).
  Please note that patients may have received multiple treatments or may have been treated in multiple regions. The tables have different lenghts, because the first table includes the information on the treated region, while the second doesn't.
• I would like to use the data of the two tables in such a way that I get the number of female and male patients for a combination of region and treatment option.

I created an example of my problem. The real tables include data of hundreds of patients, which is why i can't (don't want to) do this manually.

In this example, i'm interested in patients that were treated in Region1 and that received treatment Option1. I entered this information into the cells C13 and C14, respectively.

I know how to identify the patients treated in Region1 and with treatment option Option1 by filtering the first (green) table with the following formula:

=FILTER(B2:B10,(C2:C10=C13)*(D2:D10=C14))

The matching patients are (in this example) listed in the cells C16:C17. In practice, the number of patients and the lenght of this list may vary, depending on the querried region and treatment option.

But now i'm stuck .
How can is use the results of this formula to filter the second (blue) table and identify the number of female / male patients?

Any help would be highly appreciated!

 

[RobP]

thanks you're welcome.

Rob