Countif Question....!!!

gymwrecker

Active Member
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.

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
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
Got it. It's working perfect.

Thanks for the help!!!!

Peter_SSs

MrExcel MVP, Moderator
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)

Replies
13
Views
414
Replies
5
Views
483
Replies
1
Views
170
Replies
29
Views
1K
Replies
1
Views
774

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

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.

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

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