# Count the values displayed by Vlook up formula

#### kundanlal

##### Member
Hello Everyone,

Need small help.

Enclosed herewith a sample file cosist of table as under :
 Code Emp Name Date Emp Code CON113511 CANEE 19-Jun-19​ CON122250 Dodya 20-Sep-19​ CON126295 Sumeet 26-Sep-19​ CON132221 Pradeep 4-Jun-19​ CON132221 CON134709 Laksmikant 13-Oct-19​ CON143416 Niraj 3-Oct-19​ CON157673 Satish 16-Jul-19​ CON158908 Rajesh 29-May-19​ CON158908 CON94288 Hanamant 23-Oct-19​ CON98006 Murlidhar 20-Oct-19​ CON98006

Code, Emp Name and Date are basic data in sheet and Emp Code has been fetched by using Vlookup formula from other sheet.

Need a count of cell appearing in column "Emp Code" where the cell value starts with "CON". The count is to be done in presence of Vlookup Formula.

Thans and Regards,

Kundanlal

#### Attachments

• 11 KB Views: 5

#### trprasad78

##### Member
CHECK THE ATTACHED FILE ,
=IFERROR( IF(LEFT(VLOOKUP(A5,Sheet1!\$B:\$B,1,FALSE),3)="CON",1,"")," ")

END OF THE Column you can use sum function to add count.

#### Attachments

• 11 KB Views: 10

Thanks a lot..

#### Peter Bartholomew

##### Well-Known Member
I would argue that, if the 'Code' does not start with "CON" there is little point in looking it up; it cannot contribute to the total.
= COUNT( IFNA( IF( LEFT(Code,3)="CON", MATCH(Code, Emp_Code, FALSE) ), FALSE ) )

p.s. The defined names are simply your headings, applied using 'Create from Selection'.

#### kundanlal

##### Member
Thank you Sir.. I would try this..

#### p45cal

##### Well-Known Member
Need a count of cell appearing in column "Emp Code" where the cell value starts with "CON". The count is to be done in presence of Vlookup Formula.
=COUNTIF(\$E\$5:\$E\$14,"CON*")

#### vletm

##### Excel Ninja
kundanlal
As Your VLOOKUP-formula shows with Your sample always Emp Coded which start with letter-C or space
... shorter version ... `=COUNTIF(E\$5:E\$14,"C*")`

#### kundanlal

##### Member
Dear Sir,

But whether it will count while there is formula Vlookuobin those all cells.. I tried this but got error.. Using excel 2007 ver..

Thanks and Regards..

Kundanlal

#### kundanlal

##### Member
Please read Vlookuobin as vlookup..

#### vletm

##### Excel Ninja
kundanlal
What did Your try?
What did You got?
Send that Your tried / tested Excel-file here.

#### kundanlal

##### Member
Thanks.. attached file with both options..

#### Attachments

• 10.7 KB Views: 3

#### AliGW

##### Active Member
You can't spell!

This:

=COUNIF(\$E\$5:\$E\$14,"C*")

should be this:

=COUNTIF(\$E\$5:\$E\$14,"C*")

#### kundanlal

##### Member
Thanks.. In hurry, typo error..

#### vletm

##### Excel Ninja
kundanlal
You can write as has written or even copy&paste.