current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 2 down vote favorite

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?

share|improve this question
up vote 5 down vote

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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .