Microsoft Excel - Using COUNTIF function to assess whether a criteria is in a range

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:

14

5

58

54

36

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

Hey brian.

You can create a formula to countif(value/cell)+countif(value/cell)..... and then use the cell reference to change your criteria so you dont have to rewrite your formula everytime.


brian countif.zip

here is an example
HTH Don

Hello Brian,

You don't indicate whether your data is going to be much more extensive than you show.

If you don't mind using another row below your data range (A2:E2)(or anywhere for that matter), the following formula (copied across) will count the occurrences of each data point.

=SUMPRODUCT(($B$5:$F$14=A2)*1)

Then =COUNTIF(A3:E3,">0") will give you the result that you want.

Obviously the ranges can be extended as far as you like.

Regards

Harry

looking back your range of cell contains 14 twice 58 & 5 once so the original count I gave you would count 4 not 3

Don