count formula needed

[faraz502]

dear all,

if,

sheet1 cell a2 to b10 fill country names
&
sheet2 cell a2 to b10 fill country names
&
sheet2 cell b2 to b10 have dates

so I need date count against sheet1 country names

 

[etaf]

you should be able to use a lookup
BUT

sheet2 cell a2 to b10 fill country names
&
sheet2 cell b2 to b10 have dates

did you mean

sheet2 cell a2 to A10 fill country names
&
sheet2 cell b2 to b10 have dates

in sheet 1 b2 put

=vlookup(A2,sheet2!A2:B10,2,false)
should now return the matching country name date

 

[faraz502]

in sheet 1 b2 put

=vlookup(A2,sheet2!A2:B10,2,false)
should now return the matching country name date[/QUOTE]

Thanks dear,

but I need dates count because in sheet2 only three country names but dates are different and sheet one ten country names so I need count of dates,

 

[etaf]

sorry about that

countif( ) may help

but i'm not sure now what you want to count

can you give an example

 

[faraz502]

Sheet1

Cell A2	Cel B2
29_Cluster_02
29_Cluster_07
29_Cluster_11
29_Cluster_07
29_Cluster_07
29_Cluster_02
34_Cluster_11
29_Cluster_02
29_Cluster_01
29_Cluster_02
34_Cluster_10
34_Cluster_03
34_Cluster_09
29_Cluster_07
29_Cluster_07
34_Cluster_13
29_Cluster_14
29_Cluster_07
29_Cluster_07
29_Cluster_12

<colgroup><col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"><colgroup><col width="60" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;"><tbody>
</tbody>

Sheet2

Cell A2	Cell B2
29_Cluster_02	2014-06-15
29_Cluster_07	2014-08-11
29_Cluster_11	2014-08-22
29_Cluster_07	2014-08-18
29_Cluster_07	2014-07-04
29_Cluster_02
34_Cluster_11	2014-05-29
29_Cluster_02	2014-07-04
29_Cluster_11	2014-05-29
29_Cluster_02	2014-05-29
34_Cluster_10	2014-08-14
34_Cluster_03	2014-05-29
34_Cluster_09	2014-07-04
34_Cluster_10	2014-07-04
29_Cluster_07
34_Cluster_10
29_Cluster_14
34_Cluster_10	2014-05-28
29_Cluster_07	2014-05-28
29_Cluster_12

<colgroup><col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"><colgroup><col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"><tbody>
</tbody>

need count in sheet1on cell b2

 

[etaf]

so for example
29_Cluster_02

in the Sheet 2
29_Cluster_02
appears 4 times

but only three entries have a date

so what would be the result you need
4 or 3
OR something different and if different why can you explain the rule to apply

 

[faraz502]

need 3 count ignore blanks

thanks for your reply dear

 

[etaf]

try
=COUNTIFS(Sheet2!A:A,Sheet1!A2,Sheet2!B:B,"<>")

 

[faraz502]

try
=COUNTIFS(Sheet2!A:A,Sheet1!A2,Sheet2!B:B,"<>")

Dear Its work Thank you v much..

can we connect in future???

thanks again.

 

[etaf]

can we connect in future???

dont understand ?