Tracker
HOME    TRACKER    Excel

# COUNTIF Counting Names

## I am trying to use a countif formula to count how many students a teacher has. Here is the formula COUNTIF(fceteacher!C:C,E2) I am using a dropdown list to count how many students the teacher has.

Related Forum Messages:
CountIF: Counting Non Blanks
How can I minus 1 from this COUNTIF. Basically counting non blanks - but it keeps counting the title as well, even when i change it to start at row D2 (it just jumps back to D1 next time). =COUNTA(RAW_DATA_2!\$D\$1:\$D\$215)

Countif: Counting With Commas In Cells
how do to count the number of occurrences of a text string in a range of cells, where some cell have comma delimited entries?

I am trying to count the number of times a project number is identified in a column of cells. However, in any row in that column a cell may have multiple project numbers referenced, separated by commas.

Using countif Excel thinks that the cell has a different entry and it won’t include it in the count even though the criteria string is in the cell.

Countif - Counting Too Much (text Values)
I noticed that when I use countif to count cells with certain text value it works but up to some point when it returns way too much then (when there are generally more values matching I think). I don't know what is the cause ..formatting? some function limit ?

Countif Function For Counting Values Based On More Then 1 Variable
I'm trying to make a spreadsheet that will count the number of times a certain incident occurs, for a particular person, for a particular month. The attached spreadsheet is an example of what I need done.

For the attached spreadsheet, I need to find out how many times x employee has been late for x month, and how many times they've been late overall.

You can see one of the many tries I've attempted in the second sheet, but it doesn't seem to want to work. I have to be able to do this without VBA, because of signature issues.

COUNTIF Formula: Gives A Total Of How Many Names Are In That Range
I have certain cells in column A2:A22 that have names of people. I want a formula in Cell A23 that gives me a total of how many names are in that range. I know this is simple, but how do I put my criteria that if a cell is not blank to count it?

Counting Names In A Column
I have a list of 60,000 names in a spreadsheed, column A has the NAME, Column B the STREET NUMBER and Column C the STREET NAME. Heres a small sample.

CHALWIN E100733RD ST
JAQUE D J101263RD ST
JAQUE D J101263RD ST
JAQUE D J101263RD ST
JAQUE D J101263RD ST
JAQUE D J101263RD ST
BAXTER P M101593RD ST
BAXTER P M101593RD ST
MCLEOD E A101603RD ST
MCLEOD E A101603RD ST
MCLEOD E A101603RD ST

As you can see some of the names repeat, and that is exactly what I want. I want to write a formula in Column D that will indicate how many times each name appears so that it will show, for example, a "5" beside JAQUE D J" and a "2" beside "BAXTER P M".

I have tried using EXACT, MODE, IF ... COUNTIF wouldn't work because there will be some names that are duplicate but at different addresses, besides the range would be enormous.

Counting Formula That Counts Names
i know its out there, i just cant find it. I have a list of names in a specific pattern of cells on a spreadsheet. I would like excel to give me a number of how many names i have in this spreadsheet. I know COUNT does numbers, but is there a formula that counts names?

Counting Multiple Text Names Per Cell
I have a column that can have a single name or multiple names typed in each cell. I would like to use a vlookup table to match against the cells values. Exact matches are no problem when it is a single name, but I need a formula that matches up the name, but does not need to match the entire cell text (name1, name2, name3,...) and can count the number of cells that contained this text with in a range. In the example above, I have three names.

If those three names are listed in the vlookup table, I want to count each one so that I can sum up that company 1 appeared x number of times with in the column and is x % of all company names, company 2 appared x number of times and is x% of all companies, and so on. My formula to match exact text values looks like this: =IF(ISERROR(VLOOKUP(D4,\$H\$7:\$J\$48,3,0)),0,VLOOKUP(D4,\$H\$7:\$J\$48,3,0)) This works fine if the cell value is simply company 1, etc.

Create Array Of File Names/sheet Names
Two part question:

1) I'm relatively new to arrays, but what I need to do is generate a list of file names and the sheets within each one. I would like to use an array for this, but since I don't have much experience.... well....that's why I'm here. Can someone point me in the right direction?

2) And the second part of this.... I was planning on using the FileSystemObject to determine the files in a selected folder and loop through that list of files, opening each one and harvesting the required info (file name and all sheet names). Should I use the FSO or is there something built into Excel that might be better (and also limit the number of dependencies for this little "project" of mine).

Replace Bad Names From A List Of Good Names
create a script that will replace the names in column A on sheet1 from a Master sheet in the same workbook?

The problem is that different users are entering data on sheet1 col A in different ways example someone may enter Johnc or John C Or John What I want is for something to run down col A on sheet1 and look for the like name on the master sheet if the name matches then do nothing but if the name is like another name on the master sheet then replace the name if they are almost alike.

Looking Names In A List With Names Written Differently And With Duplicates
I am using Excel 2003 and Windows XP.

I have been given a list of my firm’s target clients (in excel) and an opportunities report (exported into excel) from our CRM system, which lists all the opportunities (i.e. opportunities to sell/provide products/services) that have been created for each client. Some of the column headings in the opportunities report are as follows:

Client; Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created etc.

What I need to do is lookup each client, from the target clients listing, in the opportunities report to see whether an opportunity has been created; and if so, return the row of values (i.e. the Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created) for that client. The result will be placed next to the name of the client in the target client worksheet.

I have a couple of problems. Initially I tried to use the VLOOKUP function to lookup the client name in the opportunities report and return the Opportunity ID (I then planned to use the same formula to return values from the other columns); however, as the client names in the target client listing were not always written the same way as they were in the opportunities report, the formula often returned #N/A. The formula I used was

=VLOOKUP(A8,'Opportunities Report'!A2:F51,2,FALSE)

So for example, the first client that I was looking up was written as “ABC Ltd” but in the opportunities report it was written as “ABC Limited”.

My second problem was that for some clients, there were multiple opportunities listed in the opportunities report. Where this was the case, there was a separate row (repeating the client name in the first column) for each opportunity created. I think that was messing up my VLOOKUP formula as well.

Is there a way to look up the client name, from the target client listing, in the opportunities report even if it’s slightly different and return the row of values for each opportunity created for that client on a separate row?

Folder Names Instead Of File Names/macro
I need to make this macro read FOLDER names instead of FILE names. When I posted this question yesterday to get this macro, I wasn't told that each file in its own folder. I need the folder names now.
_____________________________________

Sub test()
With Application.FileSearch
.NewSearch
.LookIn = "C:Ford"
.SearchSubFolders = False
.Filename = "*.*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1) = .FoundFiles(i)
Next i
Else
Cells(i, 1) = "No files Found"
End If
End With
End Sub

Pulling Out Single Names From A String Of Names
I have a list of names in a single cell. They are all seperated by a comma, then a space. Example would be: John Smith, Steve Wilson, Wallace O Malley, etc. What formula could I use to pull out the names individually, starting from the farthest right?

Compare 1st X Letters Of Names To Other Names
Here's what I'm trying to do:

In a spreadsheet I have a series of names with associated data, for instance: ...

Create A List Of Unique Names From A List Of Multiple Names
I have a database output file where one of the columns contains managers names, often more than once. I want to apply an autofilter on manager name and then copy the result to another sheet or sheets. My criteria for the autofilter is a variable pointing to a list of names that at present I maintain by hand; a for-each-next loop then cycles through the names.

What I would like to do, before running the autofilter code, is to create the list of names via code. This would then automatically pickup names that are missing.

The code I have so far is below:

Public Sub find_managers()
Dim managers1 As Range
Dim names1 As Range
Dim n1 As Variant
Dim n2 As Variant

In my mind it should check the names in the unique list against the imported list and add any missing names.

Swapping Last Names And First Names
I have a column which contains names e.g

Mr D.H.JONES
Mr Derek Harold JONES

I want last name first,then space,the other names. This is because I am using VLOOKUP and the sheet I am looking up has the names in this format.

File Names :: File Renaming Each With The Names I Have On Another List
I have a task I would like some assistance with…

I have a work book that I have to copy over 70 times for over 70 work locations. As you can see, this will require different file names for each location.

I would like some have help with a code that I can use. If possialbe I like a code that will make copies of the file renaming each with the names I have on another list. Is this feasible?

How To Countif
I have following data (two columns Parent and Child), now I want to apply Countif on Child cell.
But in Countif I want to provide the criteria...let say only count those childs whoes parent is A.

How to do this in Excel.

Parent Child
A e
A f
B g
B h
B i
C j
C k

Countif: Others
I'm reasonably new to Excel, and have a fairly basic question to check out:

I have been using the COUNTIF function to count up numbers of items in various categories in a column.

The formulae I have been using are like this:
=COUNTIF(F\$3:F\$201, "Red")

or where I've wanted to combine various comments
=SUM(COUNTIF(F\$3:F\$201,"Yellow")+COUNTIF(F\$3:F\$201,"Cream"))

I'm not sure what formulae to use to count up
1) the total number of entries in that column, so that I can make sure that I haven't missed some (without having to check manually!)

2) how to count up the values that do not match the other categories that I have specified in the COUNTIFs: this would be a value for finding how many 'other' entries there are in that column, without having to specify those values

Nested Countif?
I searched on this and didn't find what I was looking for. I want to count entries that have critieria I specify in two different ranges. Is countif the way to do this?

IF Or COUNTIF Formula?
See attached document, there are 11 cells in which will either contain Yes or No. Looking at the different combinations that there can be there can only ever be 9 out of the 11 cells being used or 10 out of 11 being used.

Also the last question (Row 25) could be filled N/A if this occurs I would like the formula not to count that. Is there a counting formula or IF formula which can be done to help me out?

Countif Macro
In col A (from A2 and down), I want to run a Countif on a Range of concatenated values in Col B (B2 and down)

I'm having trouble with the Countif part of my code

Sub countifDataRange()
Range("A2").Value = Range("B2")
Dim LastRow As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
With Range("A2:A" & LastRow)
.Formula = "=COUNTIF(Range("B2:B" & LastRow), "B" & Rows.Count)"
.Value = .Value
End With
End Sub

COUNTIF On Another Sheet
It seems I have run into yet another roadblock in my spreadsheet building. The issue is I would like to have a summary page of some of the information contained on my sheet1 of the workbook. Is there a way to use the countif function from my summary page and have it count the information on the Sheet1 which is nameed by the way (status report)

I thought this formula would work but it doesn't.

=COUNTIF('Status Report'!I11:I1958,"Ammo-com-1")

COUNTIF Function: Only If There Is Something
I am trying to count values in cells of column A only if there is something (any value) in corresponding cells in columns B, C, D, and E. If there are no values in cells of columns B, C, D, and E do not count the cell in column A.

Countif With Three Columns
I am using this formula

=COUNTIF(\$C\$1:\$C\$4000,B1)

How can I add a third column D to the formula to check if the value in 'D'
is the same

eg.
A B C D
1-Jan 1-Jan 4523
2-Jan 4523
3-Jan 4523
4-Jan 4501

COUNTIF In AutoFilter..
I have the following type of data. How can i countif if the data in Filter Mode.

DatesArea Code3-Oct-08

A4-Sep-08

A4-Sep-08

A4-Sep-08A7-Sep-08B4-Sep-08

B4-Sep-08

C4-Sep-08

D4-Sep-08

E12-Sep-08F13-Sep-08F4-Sep-08

A15-Sep-08A

If i am filtering on 4-Sep-08, i want to count how many "A". I know a method Filter by date 4-Sep-08 and area code "A". But is there any formula without filtering two columns? I have a cell down TOTAL A = ???? [ ???? What is the formula can i use? ].

Countif Arguments
The 2 basic arguments of the Countif Function (range and criteria) are simple and make sense. However, I've observed instances where the criteria component is in fact a range.

In this case, is what is the syntax instructing the app to count in the first range?

COUNTIF Not Working
I am using the following formula to count the total number of contract types if 'ITD \$K' equals '0' zero. But it returns 0 as output.

=SUMPRODUCT('PROCUREMENT CONTRACTS'!\$E\$5:\$E\$225="GROSS")*('PROCUREMENT CONTRACTS'!\$J\$5:\$J\$225=0)

SUMIF And COUNTIF
I want to calculate the average perofrmance % of 8 lines, the data isn't in one set of rows and some lines may not have values so I'm trying to account for this in my summary.

The code I'm struggling with is this...

=SUMIF(C5,C10,C15,C20,C25,C30,C35,C40,">0")/COUNTIF(C5,C10,C15,C20,C25,C30,C35,C40,">0")

COUNTIF With An INDIRECT
I need to have a cell count the number of cells that contain a certain text found in cell \$b3. (the 3 is relative the B is not).

The data will be found on multiple sheets called "Game x" where X is an integer. (Game 1, Game 2, etc...) the cells are between \$b\$65 and \$b\$73.

i tried =COUNTIF(INDIRECT("'Game " & 1:\$A\$1 & "'!\$b65:\$b73"),\$B3)
but it did not work.

also after i get that working I need to do the same thing, but i need to count the number of times the Name in \$b3 appears in the List with the word "Win" in the "D" column (next to the \$b65:b\$73)

Again i tried =COUNTIF(AND(INDIRECT("'Game " & 1:\$A\$1 & "'!\$b65:\$b73"),\$B3),(INDIRECT("'Game " & 1:\$A\$1 & "'!\$b65:\$D73"),"Win"))

Countif Column Has Value A
I want to use the countif function on the below. I want to countif col A has ‘A’ and col b has ‘liv’

BMAN
AMAN
ALIV
ALIV

Two COUNTIF Criteria
What I need to do is count the number of “Cs” in a column based on a date in another column but in the same row. I have tried something similar to this: COUNTIF(\$C\$1:\$C\$20,"=today()")+COUNTIF(\$E\$1:\$E\$20,"=complete") but is does not work. If the date in the column is less than or equal to a date specified in a cell in another worksheet, I want it to count the C in the row (if there is one).

Countif With Date
Ontvangstdatum21/08/2008AantalDatum@dagen oud22/08/200821/08/200825/08/200822/08/200825/08/200825/08/200825/08/200826/08/200825/08/200825/08/2008

In date: i have put a advanced filter with unique records. I would like to find a formula that counts howmany times that that date accurs in the first colom
I tried with a countif but you can't put a cell valume in it. i thougth something like this:
=countif(a:a;"=d") but ofcourse that doesn't work

Countif Using 2 Criteria
I have a spreadsheet with a list of months numbers and average turnarounds. Each row represents a different factor, so there are multiple rows for each month.

eg

Month Turnaround Time
10 5.2
10 6.7
11 1.1
9 8.3
11 5.4
10 6.1
etc

What I am after is something that will count the number of instances where the turnaround time is above a certain limit (eg 6.0) for each month.

Sumproduct Vs Countif
I have a worksheet where I am trying to count the number of occurences of several text strings.

For example:

I'm trying to count how many times "paid in full" and "fully paid" occur in column A.

I have two formulas, and both seem to work, but since I don't really understand either of them, I'm wondering which I should use and how I would adapt it to include additional text strings. (Like adding "paid" to the list)

Here are my formuals (I didn't write either of them, another co-worker did)

=(COUNTIF(A:A,"paid in full"))+(COUNTIF(A:A,"fully paid"))

=SUMPRODUCT(--(A1:A50={"paid in full","fully paid"}))

Also, if there is another and easier way to do what I'm trying to do, I'd love to know.

Countif Using Times
I have a long list of transactions, each has a time of the transaction. I am trying to do a count of transactions per each hour accross the day.

Countif Into Column
I have a column (o) that contains the letters ar and i need to count them into column (o 28 ) i am aware that i can use the count if formula.

COUNTIF(010:O27,"*AR")

Countif Between Two Criterias
i need to know, how much people belongs to the number in Colum A - if in
colum C is written "ISM".

A B C
1 1 Meier ISM
2 3 Huber ISM
3 2 Schmitz UPA
4 2 Mayer ISM
5 1 Mueller UPA
6 1 Hase ISM

Countif Function
I'm trying to do a count where column C="Employee" & column E="2008". Below is the formula I have tried and is obviously not working.

=countif(and(C113:C143="Employee",E113:E143="2008")

Countif And Dates
I have a spreadsheet where I am tracking dollars spent for warranty claims. The information is put in, and a date for the claim is put in as well, which is then formatted to show the date. For example, if I type in 01/13, it shows up as 13-Jan. The date column spreads from D2 to D384.

I would like to make a section that will go through the whole column and give me a total number of claims put in for jan, then in the next cell down the same thing for feb, etc etc. Basically it will be something like this:

Claims per month:

Jan 14
Feb 8
Etc,

I have been trying to use wildcards for countif, such as "*-Jan", or even just "Jan" but it is not returning any result.

Countif Funtion For More Than One IF
If I have column A with a department name e.g. A, B, C, etc
and
column B with a date next to the some of the entries of column A.

How would I compose a formula to return an answer of the amount of records that show column A as "A" and column B as "1".

This is probably immensely simple and I have a wet fish on standby to hit myself with.

Combining COUNTIF, RIGHT, <
Excel 2007

I am trying to count how many cells have the last 2 digits of 84 or less. I tried this formula, but it is not working.

=COUNTIF(RIGHT(H4:H129,2),"

COUNTIF On Dates
I have two different worksheets.

One has dates recorded as dd-mmm-yyyy (e.g., 28-Nov-2009).

On the other, I have to create a lot (over 1000) of countifs against the months/years of these dates. So, for example, if there are several dates on the first workbook that fall in Nov-09, i want to do a countif on the second workbook that tells me how many Nov-09 dates there are.

Countif Across Sheets
I have one sheet with data and want to have the data transferred automatically into another sheet.

Let's say, column A of Sheet1 contains information like
A1 - F
A2 - M
A3 - F
A4 - F
A5 - M
asf.

In Sheet2 I want to have a cell in which, f.e. the sum of all Fs is added AND kept up to date whenever I alter the information in Sheet1.

I've tried countif.3d and also sumproducts(countif(indirect...),

Countif Different Columns
I have 16 columns with 10 rows with different single digits in them. I want to count the number of times the number 2 appears in columns A, C, E, G, I, K, M, O (in other words every other column in this case).

I know how to write the formula by using countif to find the results but it is rather long. The fomrula would look like this:

Countif In More Than One Range
Is ther any way around not being able to do this - I read that if u make the ranges an array it shoul work - Shift, Control, Enter - or something but I can get it to work. I was hoping to use copuntif for this :-

COUNTIF(F33:F39,M33:M39,T33:T39,AA33:AA39,AH33:AH39,AO33:AO39,F67:F73,M67:M73,T67:T73,AA67:AA73,AH67 :AH73,"b")

How COUNTIF For Both Columns
I have two columns and have to count all "DATA PAPA" rows

How i can do it?

Using AND/BETWEEN In A Countif Formula
my current formula is =COUNTIF('Input Page'!A2:A50000,"=Monday")

i'd like to change it to check what day is in the field and then only do the above formula if that day is within the past week.

so i need the "=Monday" section to be changed to read "(is equal to monday) and (is between today and today-6)" ...todays date will be taken from 'Input Page'!B2:B50000

Countif Without Range
I have a column with multiple data entries ( dates, amounts, percentages, etc).

From these I want to count how many dates are after the selected date.

But I am unable to pickup date cells selectively.

i.e. counif ( {A1, A6, A11, A16, A21, A26}, >=1-Jan-2009)
But the function is giving error as it only accepts ranges.

I can use countif(A1:A26, >=1-Jan-2009)

But the problem is some numerical values are also in the same range ( as the numerical format of dates) - so I am unable to use it.

COUNTIF With Two Criteria
I use Excel 2003 and I have the following problem:
I have 3 columns,
A containing a list of employees (MICHAEL, BOB, MIKE, etc.)
B containing their work starting hour (8.00, 8.30, 9.00, etc.)
C containing the possible employee absence reason (ILLNESS, HOLIDAY, INJURY, etc.)

I would like to write a formula that counts the number of employees who have a work starting hour within 7.00 and are not absent. A possible table is this one:

NAME START ABSENCE
MICHAEL 6.30
BOB 8.30
MIKE 9.00 HOLIDAY
BRIAN 7.00
TOM 6.30 ILLNESS

The formula I'm looking for should calculate "2" (because MICHAEL and BRIAN are the only 2 employees starting work hour within 7.00 and not absent). As I have thought it could be useful, in another worksheet I have inserted: in A column the list of all the starting work hours: 0.00 (A2), 0.30 (A3), 1.00 (A4), 1.30 (A5), ... 7.00 (A16), 7.30 (A17), ... 23.30 (A49).

in B column the list of all the absence reasons: ILLNESS (B2), HOLIDAY (B3), INJURY (B4). I have defined 2 names, the first called EARLY_MORNING (that I have associated to the range from 0.00 to 7.00 of work starting hours column, that is A2:A16), the second called ABSENCE_REASON (that I have associated to the range (B2:B4) of absence reasons' column). What kind of formula can I write to obtain what I want (using the 2 names EARLY_MORNING and ABSENCE_REASONS defined in the other worksheet)?