Any of these in any of these

[FLETCH515]

I am attempting to get a count of all the cells in K:K, that contain the same value as any of c12:c43 from a different tab. I got it to work by using a long string of countifs, but that seems like way to much work for excel. I have tried using SUMPRODUCT, SEARCH, ISNUMBER, COUNTIFS,ETC. This one has me stumped. Any help would be greatly appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
How about
=SUM(COUNTIF(K1:K5000,Sheet1!C12:C43))

This is an array formula & needs to be confirmed with Ctrl Shift Enter, rather than just Enter

 

[FLETCH515]

Fluff, you are a life saver. Worked beautifully. I guess I need to learn about these array formulas.

 

[FormR]

Hi, FWIW, you can avoid the need for array entry with:

=SUMPRODUCT(COUNTIF(K1:K5000,Sheet1!C12:C43))

You should also note, that both of these are really just a shorter form of summing the individual COUNTIFS() - they don't necessarily cause Excel any less work.

 

[Fluff]

You're welcome & thanks for the feedback