Countif Question....!!!

gymwrecker

Active Member
Joined
Apr 24, 2002
Messages
354
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I have the following:

Worksheet 1

A1=Mr. John Doe
A2=Mrs. Jane Doe
A3=Mrs. Jane A. Doe
A4=Mr. John Doe

Note: In each of these cells (A1:A4) I'm using a Data Validation list (drop-down meny) including the names above.

Worksheet 2

The formula in A1 must count how many times Mr. John Done repeats within the range. A2, the same formula, but for Mrs. Jane Doe.

Any advice?

Thanks!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Gymwrecker:

Let me see if I have understood you correctly ...
Book1
ABCD
1Mr. John Doe
2Mrs. Jane Doe
3Mrs. Jane A. Doe
4Mr. John Doe
5Mr. John Doe
6Mrs. Jane Doe
7Mrs. Jane A. Doe
8Mr. John Doe
Sheet1


Then Sheet2 ...
Book1
ABCD
1NameCount
2Mr. John Doe4
3Mrs. Jane Doe2
4
Sheet2


formula in cell B2 is ... =COUNTIF(Sheet1!$A$1:$A$8,Sheet2!A2)

this is then copied down.
 

gymwrecker

Active Member
Joined
Apr 24, 2002
Messages
354
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Got it. It's working perfect.

Thanks for the help!!!!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,364
Office Version
  1. 365
Platform
  1. Windows
A small point, but since the formula is on Sheet2, the reference to Sheet2 in the formula should not be needed. That is, this should do the same job:
=COUNTIF(Sheet1!$A$1:$A$8,A2)
 

Forum statistics

Threads
1,181,607
Messages
5,930,877
Members
436,764
Latest member
avalladarez

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
Top