Combining COUNT IF AND VLOOK UP EXCEL

excel excel-formula vlookup
Kiwi picture Kiwi · Mar 22, 2013 · Viewed 174.7k times · Source

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

Answer

mechanical_meat picture mechanical_meat · Mar 22, 2013

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.

Related questions

Excel - find cell with same value in another worksheet and enter the value to the left of it

I have a report that is generated in Excel which contains an employee's number, but not his/her name. Not every employee will be on this worksheet on any given day. In a 2nd worksheet I have a list of …

Read More
Check if an excel cell exists on another worksheet in a column - and return the contents of a different column

What I want to do is to say if the contents of cell D3 (on current worksheet) exist in column A in the first worksheet (in my case entitled list). (and they always do exist somewhere). Return the contents of …

Read More
How to use VLOOKUP result as COUNTIF criteria

I want to be able to determine how many times a person's particular ID (e.g. #123) has appeared in a separate column (e.g. column C) given a name (e.g. Bob). I have: Let column A consist of several …

Read More