Count the values displayed by Vlook up formula

[kundanlal]

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

 

[trprasad78]

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.

 

[kundanlal]

Thanks a lot..

 

[Peter Bartholomew]

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]

Thank you Sir.. I would try this..

 

[p45cal]

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]

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]

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]

Please read Vlookuobin as vlookup..

 

[vletm]

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

 

[kundanlal]

Thanks.. attached file with both options..

 

[AliGW]

You can't spell!

This:

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

should be this:

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

 

[kundanlal]

Thanks.. In hurry, typo error..

 

[vletm]

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