I have 2 sheets.

Sheet1 has a layout with numbers in columns C to R on over 500 rows. In these cells are numbers sometimes the same number appears more than once.

Sheet2 in column A there is a list of Unique numbers.

I am trying to figure out a formula that will see if the numbers in sheet2 column A appear anywhere on sheet1 and count the number of times they do. If they do not, show 0 or "not found".

I found this: =IFERROR(IF(MATCH(A1,Sheet1!$A:$A,0),"yes",),"no") but I can't figure out how to get it to count the number of times a number reoccurs and look at columns C to R.

Any Idea before I do it in PHP?


The formula you are looking for is =COUNTIF(Sheet1!C:R,A1).

This formula would go into B1 and copied down for every row in column A on Sheet2.

That will return 0 or more, depending on the number of times it finds that value.


When you are applying a formula to an entire range, if becomes an array formula. When you use your formula, dont just press ENTER, instead ; press CTRL+SHIFT+ENTER


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.