Combining COUNT IF AND VLOOK UP EXCEL

Question

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

User · 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).

User · Answer

Try this    IF NOT ISERROR MATCH A3 worksheet2 A A 0    COUNTIF worksheet2 A A A3   No Match Found

User · 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

User · 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

User · Answer

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

[excel] Combining COUNT IF AND VLOOK UP EXCEL

Examples related to excel

Examples related to excel-formula

Examples related to vlookup