Menu
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
503
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.
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
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 (…))
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:
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.
58
=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!
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
