Google Sheets help - How to apply COUNTIFS with different size criteria ranges

Question

I understand that for COUNTIFS, you need to have ranges that are the same size and shape. However, I need one condition to be that the ID# in the cell matches the ID # in a one column range AND that the age in the cell matches the age value in a five column range. How can I work around this?

Sample sheet: https://docs.google.com/spreadsheets/d/1i6qFmJ1t7yDpGVSO8YOPnWqW_OZEz6fv4shTOBO-q3M/edit?usp=sharing

Answer 1

In cell A7 of the 'Analysis' tab I entered

=query(ArrayFormula(regexreplace(split(flatten(Masterfile!A2:A&"_"&Masterfile!B2:F), "_")&"", "\d+\smonths", "0")), "Select Col1, count(Col1) where Col2 <>'' group by Col1 pivot Col2")

See if that works for you?