count formula needed

faraz502

Board Regular
Joined
Mar 26, 2014
Messages
127
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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
 
Upvote 0
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,
 
Upvote 0
sorry about that

countif( ) may help

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

can you give an example
 
Upvote 0
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_022014-06-15
29_Cluster_072014-08-11
29_Cluster_112014-08-22
29_Cluster_072014-08-18
29_Cluster_072014-07-04
29_Cluster_02
34_Cluster_112014-05-29
29_Cluster_022014-07-04
29_Cluster_112014-05-29
29_Cluster_022014-05-29
34_Cluster_102014-08-14
34_Cluster_032014-05-29
34_Cluster_092014-07-04
34_Cluster_102014-07-04
29_Cluster_07
34_Cluster_10
29_Cluster_14
34_Cluster_102014-05-28
29_Cluster_072014-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
 
Upvote 0
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

 
Upvote 0
need 3 count ignore blanks

thanks for your reply dear




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

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top