• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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 :
CodeEmp NameDateEmp Code
CON113511CANEE
19-Jun-19​
CON122250Dodya
20-Sep-19​
CON126295Sumeet
26-Sep-19​
CON132221Pradeep
4-Jun-19​
CON132221
CON134709Laksmikant
13-Oct-19​
CON143416Niraj
3-Oct-19​
CON157673Satish
16-Jul-19​
CON158908Rajesh
29-May-19​
CON158908
CON94288Hanamant
23-Oct-19​
CON98006Murlidhar
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

  • Sample.xlsx
    11 KB · Views: 5
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

  • Sample.xlsx
    11 KB · Views: 10
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'.
 
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
 
Back
Top