I am attempting to use the COUNTIFS function to return the count of the criteria that are within a given range. For example:

Range Data:

Criteria Matrix:

Most Frequently Chosen Frequency Matrix: |
14 |
20 |
15 |
2 |
32 |

22 |
7 |
16 |
25 |
29 |

32 |
17 |
2 |
57 |
51 |

42 |
41 |
35 |
20 |
6 |

41 |
14 |
20 |
58 |
33 |

52 |
31 |
29 |
45 |
20 |

19 |
38 |
8 |
7 |
41 |

29 |
5 |
18 |
43 |
1 |

6 |
8 |
24 |
41 |
24 |

7 |
22 |
1 |
12 |
26 |

The output that I am trying to create is "3" as the #'s 14, 5, & 58 in the range data also occur in the criteria matrix.

The formula that I have attempted is:

COUNTIFS(range data,14,range data, 20, range data, 15,...range data, 26)

The output can be created using an IF(OR() formula, but there are a large number of combinations that will make this formula very long. I hope that there may be a more effective solution.

Thanks,

Brian