I have multiple spreadsheets in a `workbook`

and I would like the following in basic `English`

talk:

IF `worksheet1(cell)A3`

, appears in ‘worksheet2’ column B – count how many times it appears in column b ‘worksheet 2’

So in other words – Lets say `A3`

= BOB smith – in work sheet 1

and appears 4 times in worksheet 2 – I want the formula to count the fact that `A3`

‘Bob smith’ is in worksheet 2 4 times, and come back and tell me 4.

I have attempted to do separate calculations – with use of Vlookups – then in another cell to count/do if statement

for example

```
=COUNTIF(VLOOKUP(A9,'To retire'!J:J,9,1))
=IF(J228=O233, 'worksheet2'!F440,0)
=VLOOKUP(A3,'worksheet2'!A:A,1,1)
```

Help would be very much appreciated, I am very stuck – I am unsure if I am looking into this too deeply or not enough! Thank you in advance

This is trivial when you use `SUMPRODUCT`

. Por ejemplo:

```
=SUMPRODUCT((worksheet2!A:A=A3)*1)
```

You could put the above formula in cell B3, where A3 is the name you want to find in `worksheet2`

.

### Answer：

=COUNTIF() Is the function you are looking for

In a column adjacent to Worksheet1 column A:

```
=countif(worksheet2!B:B,worksheet1!A3)
```

This will search worksheet 2 ALL of column B for whatever you have in cell A3

See the MS Office reference for =COUNTIF(range,criteria) here!

### Answer：

You can combine this all into one formula, but you need to use a regular `IF`

first to find out if the `VLOOKUP`

came back with something, then use your `COUNTIF`

if it did.

```
=IF(ISERROR(VLOOKUP(B1,Sheet2!A1:A9,1,FALSE)),"Not there",COUNTIF(Sheet2!A1:A9,B1))
```

In this case, **Sheet2-A1:A9** is the range I was searching, and **Sheet1-B1** had the value I was looking for (“To retire” in your case).

### Answer：

Try this:

`=IF(NOT(ISERROR(MATCH(A3,worksheet2!A:A,0))),COUNTIF(worksheet2!A:A,A3),"No Match Found")`

### Answer：

If your are referring to two worksheets please use this formula

```
=COUNTIF(Worksheet2!$A$1:$A$50,Worksheet1cellA1)
```

In case referring to to more than two worksheets please use this formula

```
=COUNTIF(Worksheet2!$A$1:$A$50,Worksheet1cellA1)+=COUNTIF
(Worksheet3!$A$1:$A$50,Worksheet1cellA1)+=
COUNTIF(Worksheet4!$A$1:$A$50,Worksheet1cellA1)
```

Tags: excelexcel