Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Combining COUNT IF AND VLOOK UP EXCEL

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

like image 503
Kiwi Avatar asked Mar 22 '13 16:03

Kiwi


People also ask

How to combine VLOOKUP and countif into one formula?

See the MS Office reference for =COUNTIF (range,criteria) here! 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.

What is the if VLOOKUP formula in Excel?

Translated in plain English, the formula instructs Excel to return True if Vlookup is true (i.e. equal to the specified value). If Vlookup is false (not equal to the specified value), the formula returns False. Below you will a find a few real-life uses of this IF Vlookup formula. Example 1. Look up a specific value

How do I use the IFNA VLOOKUP combination in Excel?

In the recent versions of Excel 2016 and 2013, you can use the IFNA Vlookup combination again: This is one more variation of the "Vlookup if then" statement: return nothing when the lookup value is not found. To do this, instruct your formula to return an empty string ("") instead of the #N/A error: IF (ISNA (VLOOKUP (…)), "", VLOOKUP (…))

What is an example of if condition in Excel VLOOKUP?

Compare Vlookup result with another cell Another typical example of Vlookup with If condition in Excel is comparing the Vlookup output with a value in another cell. For example, we can check if it's greater than or equal to a number in cell G2:


2 Answers

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.

like image 58
mechanical_meat Avatar answered Sep 20 '22 15:09

mechanical_meat


=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!

like image 20
Andreas GS Avatar answered Sep 21 '22 15:09

Andreas GS