Tracker
HOME    TRACKER    Excel

# 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")

Related Forum Messages:
Countif- Sheet That Contains Dates Going Back A Few Years
I have a sheet that contains dates going back a few years. I am trying to use the countif function to count the different sites in column B according to date. eg I want to be able to find out how many jobs for PHO1 were created between todays date and 7 days ago, this can go into column C, In column D I need to find out how many jobs were created by PHO1 between 7 and 14 days ago etc etc....

IF Statement Within A COUNTIF Statement: Cell In Sheet "Summary" Count The Number Of Cells In Column DX Of Sheet "Analyses" That Are Greater Than 0
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."

(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:

=COUNTIF(IF(Analyses!\$A\$1:\$A\$10000=Summary!B8,Analyses!\$DX\$1:\$DX\$10000,""),">0")

(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.

COUNTIF (b4:b65000= &quot;Name&quot; Then Countif G4:g6500=&quot;BI&quot;)
I have a simple database spread sheet and I need to count a column under certain conditions. In one column I have employee names that appear repeatedly, in another I have codes. I want to be able to count how many times the code appears next to the name.

For instance:
If b4:b65000 = Sam Douglas then I want to count how many times different codes appear in the adjacent cell.

Sam Douglas:BI
Sam Douglas:BI
Sam Douglas:SI
Sam Douglas:BI

BI = 3
SI = 1

Creating New Sheet From Template Sheet & Filling In Summary Sheet - Userform
I have some experience with excel, but until now have not ventured into VBA and macros.

I have a workbook which will have the following sheets:

1.Absence Summary sheet - Summarises data from each employee's individual sheet.

2. Template Sheet - A sheet formatted as an absence record sheet, but without data.

3. Individual employee Absence record sheets - Based on the Template sheet.

I have read with interest the various posts and help files on User Forms & Macros, but have got a bit stuck.

My Aim: ....

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

Copy Sheet & Create New Monthly Sheet From Present Sheet
I want to create a macro button that can create copy, insert, paste and rename the new sheet in next month's name, like if the active sheet's name is January, I want to copy the whole sheet of January, insert new sheet, paste the new sheet and rename the new sheet to next month like February?

Also rename the new sheet (February) cell B3 the same as new sheet's name (February)

So if month of February is near end, the macro button in February will create the same way as Jan did which means the next sheet will be named March and so on.

Copy Data From One Sheet (Fixed Cells And Sheet) To Another Sheet
I want to be able to copy a name from one sheet (Available Players), paste it to a cell in another sheet (Round 1 through Round 20). The cell that will be copied is fixed but the place where it will be pasted will be different and may be on a different sheet.

also i would like to change the color of the copied cell to "greyed" out or cut if it can not be greyed out. I have created a button and put in a macro that i created but have been having problems with it, generic 1004 errors that i can not figure out. i am attaching the document.

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 And Looping In VBA
I am trying to automate an AvgCustomerSpend calculation I do on multiple columns copied from pivots. I can get snatches of VBA code, but get stuck on syntax for a lot of things.

The calculations are on columns 3..n, where n is never greater than 20 or so. The numerator is the sum of the column. In the manual version, the denominator uses a compound COUNTIF formula on the column because the data contains both blanks and zeroes. "0" as the condition for COUNTIF() doesn't give the right results.

=COUNTIF(C:C,">0")+COUNTIF(C:C,"

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 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 To See Of There's A Same Value In Each Of These Ranges
I have 2 ranges with values, and I want to use countif to see of there's a same value in each of these ranges.

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, LOOKUPS And ROW
Sheet 1 has a data entry sheet - with a list of Local Authorities down the left, and criteria against which they are scored along the top. They either score, 1, 2, 0, or 'Unknown.' The order may be changed through sorting.

Sheet 2 is a summary, and I need to count how many 'unknowns' there are for each line.

I can't figure it out. And I am sure it is dead easy. In my defense I have been in bed ill for a week, and my brain isn't firing on all cylinders.

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 And Sum When Name / Sum Changed
when the name changed and sum changed.

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?

Countif Odd Numbers
I have a list of integers in a column, and I would like to count how many odd numbers there are. I tried using COUNTIF(D1:D33,MOD(,2)=1) and similar variations, but none seem to work. The numbers are ascending as the rows increase, but there can be blank cells in the column.

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

Filter & COUNTIF
This is a Cross-Post because when I tried to post this on the Mr Excel Forum, it did not work properly with the MHT files I used. Mr Excel Forum requires an add-in to Excel called: HTML Maker 2.42, however, I was unable to obtain HTML Maker 2.42 from the owner's website or any alternate websites. Plenty of people viewed my message but no replies. In the attached workbook named: Test 2.xls please view the worksheet named: Summary. On this worksheet the user (user has very little experience in Excel 2003) selects an Operation in cell B2 via a drop-down list and a Task Group in cell B4 via a drop-down list.

After the user has made their selections, I need Excel 2003 to automatically Select worksheet named: Data. I then need it to automatically filter the user selected Operation in column B and the user selected Task Group in column C. Once the filtering is complete, I need Excel to automatically calculate the COUNTIF functions as follows: on columns E and U and populate the results back on to the worksheet named: Summary as follows:

Cell Summary!B8: =COUNTIF(Data!E\$3:E\$65000,"PERMANENT")
Cell Summary!B9: =COUNTIF(Data!E\$3:E\$65000,"FUTURE")
Cell Summary!B10: =COUNTIF(Data!E\$#:E\$65000,"TEMPORARY")
Cell Summary!B11: =COUNTIF(Data!E\$3:E\$65000,"FUTURE DELETION")
Cell Summary!B25: =COUNTIF(Data!U\$3:U\$65000,"Y")

Operations has a defined name = Operations!\$A\$2:\$A\$12.

Task Group has a defined name = Groups!\$A\$2:\$A\$29.

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)?

VLOOKUP And COUNTIF Together?
My boss has asked me to work this one out and is putting me under great pressure to resolve it. I have tried Vlookup and COUNTIF etc but just cannot get to make it work. I have attached an example file but to explain:

I have a database of inspectors faults, in column B, I have the week number, column*C, I have the part number & column C the fault category (this includes OK). The actual database is up to 5000 rows now.

What I need to be able to do is in one cell (column H2) count all the OK's for week 1 and all the Not OK's (column I2) in week 1 and the same for week 2 (column H3) and week 3 (column H4) and so on for the whole year. I have tried a vlookup using cell G2 (containing "1") as the search and the week number column as the range and then counting if OK, but it never worked!

Countif For More Than One Condition
I have a table with 2 columns - Gender & Age. Now, suppose I want to know the number of Males in the table with age greater than 14, how do I do that? Can a Countif formula be used for more than one condition or is there some other formula?

COUNTIF On Two Columns
I've been asked to extend the counting capabilities of my interface to pick up the profession of each person when they answer whatever is selected in the 'Interface' worksheet (cell B2) put them in my interface.

I've been trying various incarnations of SUMPRODUCT along with what DonkeyOte helped me with before, but currently no joy. Hope that makes sense - have a look at previous post for further details. I've attached an example to look at.

Countif With Two Variables
I'm trying to count cells in one column that match a variable only if it also matches a variable in another column. For example, I want to count all of the cells in column A that match "Franklin" only if column D shows "True".

COUNTIF With Two Conditions
I am trying to create a COUNTIF formula which will work with two conditions. If you see the attached spreadsheet you will find the data that I am trying to apply a formula to. I have my data in the table on the right. The table on the left is supposed to show the number which the number of destinations that had a certain range of visitors.

As you will see that there are 3 destinations that had 12 or more million visitors, this was counted using a basic COUNT IF formula but for the rest of my data how can I apply the formula so that the correct number of destinations are counted. For example what formula would be needed to count the number of destinations that have had 8-11.9 million visitors. I am guessing that the formula will have the conditions ">=8" and "<=11.9"?

Sum And Countif From Two Cells
I have a formula =IF(AND(F14=3,G14<2)=TRUE,1,"") which is in cell J14. I also want to 'add' to this formula an 'or statement' (I am not sure of the terminology, so taking a shot in the dark). So it would continue to complete the calculation as normal, but if there is a '1' in K14 it would also output in J14 as a 1