Help! Counting First and Last Names

**Carolyn Phillips** · 04-14-2014, 10:07 AM

Hi, I have a file I'm working on for a physiotherapist. It lists all patient appointments and she wants to be able to count how many appointments a particular patient has had. I have been trying to use the COUNTIF formulas to say if First name = X and last name = y to count how many instances they appear (this needs to be done as some have the same first names or surnames).

E.g

Joe Blogs
Fred Mcfarlan
Billy Friday
Billy Friday
Billy Friday
Billy Tuesday
Billy Tuesday

So I want to count how many times Billy Friday has had appointments with me, what formula would I use to get that data?

Any help would be much appreciated!

Carolyn

**Jonmo1** · 04-14-2014, 10:17 AM

Try countifS

=COUNTIFS(A1:A100,"Billy",B1:B100,"Friday")

**AlKey** · 04-14-2014, 10:19 AM

Try this one

=COUNTIFS(A2:A7,"Billy",B2:B7,"Friday")

	A	B	C
1	Joe	Blogs	3
2	Fred	Mcfarlan
3	Billy	Friday
4	Billy	Friday
5	Billy	Friday
6	Billy	Tuesday
7	Billy	Tuesday

**mikeTRON** · 04-14-2014, 10:32 AM

You want the COUNTIFs formula which allows for multiple criteria (as in look for first name then look for last name and count all that match).

See the attached.

CountIFS.xlsx

**Carolyn Phillips** · 04-14-2014, 11:10 AM

Originally Posted by Carolyn Phillips

Hi, I have a file I'm working on for a physiotherapist. It lists all patient appointments and she wants to be able to count how many appointments a particular patient has had. I have been trying to use the COUNTIF formulas to say if First name = X and last name = y to count how many instances they appear (this needs to be done as some have the same first names or surnames).

E.g

Joe Blogs
Fred Mcfarlan
Billy Friday
Billy Friday
Billy Friday
Billy Tuesday
Billy Tuesday

So I want to count how many times Billy Friday has had appointments with me, what formula would I use to get that data?

Any help would be much appreciated!

Carolyn

So I did that example and it works, then I try to replicate it on the live data and it's coming back with a 0 - why does it do that?

**Carolyn Phillips** · 04-14-2014, 11:11 AM

I did the example, it worked, I then replicated it on the live data and its coming back with a 0 - I don't understand why it's not working!

**Jonmo1** · 04-14-2014, 11:11 AM

It would return 0 if none of the rows met the criteria.
Check for exact spelling, and extra spaces in the cells.. "Billy" vs " Billy" vs "Billy "

Can you post a sample workbook?

**ChemistB** · 04-14-2014, 11:12 AM

To post a workbook, Go Advanced>Manage Attachments (or click on paperclip icon)

**Carolyn Phillips** · 04-14-2014, 11:17 AM

Here you go - not used this forum before, so hopefully it will attach!

**AlKey** · 04-14-2014, 11:21 AM

Try this now. You have trailing spaces in column A

=COUNTIFS(A1:A6,"Jean*",B1:B6,"Doe")

**Carolyn Phillips** · 04-14-2014, 11:31 AM

Wow - thank you. Something so silly as a space can really muck those formulas up. Now for my next question - how do I easily pull that formula down to make the same formula work for a certain set of patients without having to update all the names manually in the list?

**AliGW** · 04-14-2014, 11:36 AM

Carolyn - you need to refer the formulae to two cells where you can input the names.

=COUNTIFS(A1:A6,E1&"*",B1:B6,E2) where E1 is the cell into which you type the first name and E2 the last name.

**AlKey** · 04-14-2014, 11:38 AM

You would have to create a table with names so you could reference them in your formula instead of hard-coded values.

Something like this

=COUNTIFS($A$1:$A$6,D1,$B$1:$B$6,E1)

	A	B	C	D	E
1	Jean	Doe	2	Jean	Doe
2	Lyn	Farley	2	Lyn	Farley
3	Herbert	Organza	1	Herbert	Organza
4	Lyn	Farley	1	Phil	Grover
5	Phil	Grover
6	Jean	Doe

**Carolyn Phillips** · 04-14-2014, 12:54 PM

Thanks, it worked!

**Carolyn Phillips** · 04-14-2014, 12:57 PM

Now for my next question. I now want to add up revenue by customer, so I have my customer database to reference the name, and my appointments with income. So my query is: if first name = jean and second name = pitney in appointments then add up the income next to their names...

Customer Database
Jean Pitney

Appointments

Jean Pitney £25
Jean Pitney £45
Jean Pitney £85
Jean Pitney £105
Harold Organza £145

**AlKey** · 04-14-2014, 12:58 PM

You're welcome. Please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

**ChemistB** · 04-14-2014, 01:22 PM

Now you want the SUMIFS function

=SUMIFS(SUMRANGE (i.e. C1:C12), range 1, criteria 1, range 2, criteria 2)

**mikeTRON** · 04-14-2014, 02:22 PM

Why not build a pivot table and use the native filter functionality to count occurrences?

Help! Counting First and Last Names

LinkBack

Thread Tools

Rate This Thread

Display

Help! Counting First and Last Names

Re: Help! Counting First and Last Names

Re: Help! Counting First and Last Names

Re: Help! Counting First and Last Names

Re: Help! Counting First and Last Names

Re: Help! Counting First and Last Names

Re: Help! Counting First and Last Names

Re: Help! Counting First and Last Names

Re: Help! Counting First and Last Names

Re: Help! Counting First and Last Names

Re: Help! Counting First and Last Names

Re: Help! Counting First and Last Names

Re: Help! Counting First and Last Names

Re: Help! Counting First and Last Names

Re: Help! Counting First and Last Names

Re: Help! Counting First and Last Names

Re: Help! Counting First and Last Names

Re: Help! Counting First and Last Names

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Counting names in a column but counting duplicate names once

Counting Unique names

Counting up names

Counting Names

Counting colours and names

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions