I have a list of records where the Project Manager has several numbers
assigned to him but sometimes the number is duplicated. How do I use the
CountIF so that it only counts the number once? Here is an example of my
Sales Order Project Mgr
if you want a count per Project Manager use the following formula
...Countif's and And functions
I need a formula that will take:
Countif(a2:a7="EA" AND b2:B7="set")
Obviously this isn't a valid formula but trying to accomplish this.
Found my answer is another persons question/reply:
so the answer is SP :-)
(remove nothere from the email address if mailing direct)
"JoatNIC" <JoatNIC@discussions.microsoft.com> wrote in message
> I need to count the number of cells in Column A that contain an S, AND t...COUNTIF for 2 columns
I'm trying to set a parameter of the following in a formula in column J:
That also only counts the number of times in column J where a cells (J1:J299)
value is greater than "0". ----this is where I can't figure out the how
In other words, I need to set-up a formula in column J row 300 that I want
the number of times that column C is equal to cell B300 only if the value in
column J's cell is greater than zero.
"b1...Vlookup, COUNTIF, IF or Nested
I have a work sheet "Raw Data" of about 1500 rows and 30 Columns. In columns
K, K, L, M, T and W are names of staff. In worksheet "Lookups" I have a
defined list of "Leavers".
I need to return the names of the leaver/s in the last column of the "Raw
Row 15 Column K contains "Joe Blog" Column M contains "Micky Mouse" and
Column W contains "Jane Doe". in my defined list of leavers "Joe Blog" and
"Jane Doe" are listed, in the last column of the "Raw Data&quo...COUNTIF #16
I want to count how many numbers in a range are less than a given value.
However, I don't want that given value to be hardcoded in the COUNTIF formula.
For example: I want this formula: =COUNTIF(P4:P1073,">=-0.0045")
to look something like: =COUNTIF(P4:P1073,">=V4")
However that second formula doesnt seem to work. Any ideas?
(remove nothere from the email address if mailing direct)
"adamcal" <email@example.com> wrote in message
I need to count the number of FALSE values returned in another worksheet
based on 'and' function in that column. I am using the following function:
=COUNTIF('Main Entry Sheet'!A26:A463,"'Main Entry Sheet'!AN26:AN463=FALSE")
but I get a 0 value returned as "=and........" is the true value of the
cells rather than the returned "TRUE/FALSE"
You want to count the cells in A26:A463 based on the condition that the
corresponding cell in AN26:AN463 has FALSE value? It would seem that the
answer will be exactly the s...COUNTIF function question...
So I have a spreadsheet where we enter customer data and then we use a
COUNTIF on another sheet to count several categories (Sales Rep, type,
etc). What I am trying to find out is if I can add up the sales per
rep, whose names are entered in column G, but only count them if there
is a certain entry in column D. Column D has 6 options, and if 2 of
them are selected, I want the sales rep's numbers to be excluded from
the data collection. Does this make sense?
I know what I want the function to say, just don't know how I should
"C...Countif that can i half
I was wondering if anyone could help with a formula I have a holidday
spreadsheet that tracks employees holidays and sick throughout the year at
I have a colum that counts the number of holidays ane employee taske but I
am not sure how to count half days can any one help my formula is below
You say you have a column but you reference M6:GL6
Please tell us the exact layout of the worksheet:
employees name in A2
holidays taken in M2:GL2 using the code Bh
and half h...Countifs formula not calculating my specific data
I am working with a Countifs formula, and it is not returning the correct
information when I am using the specific data I want, but if I simplify it,
it will return what I want. i.e.
^THIS IS NOT WORKING, BUT......
=COUNTIFS(A3:A500,"X",B3:B500,"Y",C3:C500,"Z") does work, why is it doing
this? Would truley appreciate any help.
>^THIS IS NOT WORKING...Countif in two columns and in different cells
I'm not sure which function I need to add text "p" and "cps" when this
data is in different columns. The total in this ex: would be p+cps = 2.
Any help would be greatly appreciated. I'm new at this.
total p+cps = 2
> I'm not sure which function I need to add text "p" and "cps" when this
> data is in different columns. The total in this ex: would be p+cps = 2.
> Any help would be gre...nesting with countif
I am having trouble with nesting the and command in a sheet I am
attmepting to prepare a spreadsheet to keep track of the clients in the
various programs at the not-for-profit blind services agency where I work.
I pasted a small piece of the sheet below. I used the countif function in
the formulas in column R below
=COUNTIF($S$25:$S$31,"Alp II", =COUNTIF($S$25:$S$31,"Alp III",
=COUNTIF($S$25:$S$31,"Alp II E", and =COUNTIF($S$25:$S$31,"Ancillary to add
up the how many people were in each program, but I have not been able to
figure out how to count for ...COUNTIF NAME RANGE
I have one worksheet called with column A1:A72 filled with 6 digit
numbers in each cell - i.e. 239260 (A1), 239060 (A2), etc. all the way
down to A72 with 6 digit numbers.
I have a second worksheet called query parameters. On it is a named
range called SHIPSEA which is another list of 6 digit numbers in a
column that identify stations that are assigned these 6 digit numbers.
The numbers in this column identify SHIPSEA.
How do I use a CountIF formula to count the number of instances that
SHIPSEA numbers appear in the worksheet one column (A:A)?
My formula returns a zero:
This content works individualy, how can i make it work? is it any way to
count the values between A1> and <B1?
The simplest is
> This content works individualy, how can i make it work? is it any way to
> count the values between A1> and <B1?
If your version of Excel uses the semi-colon as a separator, replace
commas with semi-colons.
Hope this helps!
In art...Countif gone wrong
A B C D E
2000BC DEF DEF DEF 2000BC
ABC 2000BC DEF 2000BC DEF
2000BC 2000BC 2000BC 2000BC 2000BC
ABC 2000BC DEF DEF DEF
This normally works but Im having a problem with it now. My formula
looks like this
The column range is a lot of lines ie reason why I use column:column, the
last countif was added & it returns answer as 0 instead of adding 2 more-if I
change the formula...CountIf Limitation?
I have a column that contains three text descriptions ie.
Intermediate Average or Advance. Using the CountIf
function , I am able to count each description separately
and then show the overall total.
Can I modify the Countif to add up the three descriptions
and display a single total of their occurance?
Thanks very much
In article <firstname.lastname@example.org>,
"Deltaecho" <email@example.com> wrote:
> I have a column that conta...Using COUNTIF (specific cells over multiple sheets) � can anyone help?
I�d be really grateful if someone could assist me in how to �formula
I have twelve identically formatted worksheets (�A� through to �J�)
In each of the cells B6:F6, B10:F10, B14:F14, B18:F18 & B22:F22 on eac
sheet there will be a different one of 5 words (each word can and wil
occur more than once within the above range). For the purpose of thi
exercise I need to ignore the text in the other rows (eg 7-9, 11-1
What I need to tally on a separate sheet within the workbook is ho
many times each of the five words occur in the group above (eg B6:F6
B10:F10, B14:F14, B1...COUNTIF #15
I have a question regarding the COUNTIF function.
How do I use this function to count the number of times a
record appears when I only enter a portion of the search
eg. I have a column of data that, among other things,
contains text as "anautoalert". How do I write the
COUNTIF to simply count the number of times that "auto"
"James" <firstname.lastname@example.org> wrote in message
> Hi everyone,
> I hav...Countif #12
Thanks for the help, that worked out great
patrick s.'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1469
View this thread: http://www.excelforum.com/showthread.php?threadid=26318
...Dcount or Countif?
I have a spreadsheet with 5 columns and need to count the number of
responses between a range based on the criteria of two cells.
For instance the range is A1:E16
A = Name
B = Address
C = City
D = State
E = Age
A1 = Jack
D2 = Michigan
Age = 20-29, 30-39, 40-49, 50-59
I need an equation that will count the number of records with a name
of Jack and a state of Michigan and filter that count to a range. I
will drop the count for each range in a different cell so the equation
would be a static age bracket.
Can anymore make any suggestions?
On Tue, 7 Apr 2009 ...CountIF multiple conditions
I have been searching for some help:
I have multiple columns and 2000 rows with numbers
I want the to count only those instances where the number 2 appears in
AND the number 5 also appears in columns B
Thanks for the help
Adjust the ranges to match--but you can't use whole columns (except in xl2007).
=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.
Bob Phillips explains =sumproduct() in much more detail here:
I would like a formula which will allow me to calculate the number of mondays
in a month which are either C- closed, H- holiday. or TT- term from the
following list. This will be duplcated for each month. I have tried to use:
=COUNTIF(B$2:B$32,"Monday")+COUNTIF(C$2:C$32,"H") but this adds the day and
Can anyone help.
Date April '10
1 Thursday H
2 Friday C
5 Monday C
6 Tuesday H
7 Wednesday H
8 Thursday H
9 Friday H
12 Monday H
13 Tuesday TT
14 Wednesday TT
15 Thursday TT
16 Friday TT
17 ...COUNTIF?? SUMIF??
I need to count the rows only if these two conditions exist in the same row.
Column A must be a specific numerical value and column B must contain any
I assumed your range is row 1:100, change to fit your data:
> I need to count the rows only if these two conditions exist in the same row.
> Column A must be a specific numerical value and column B must contain any
I need to run a countif formula, with 2 conditions on two different
columns, The first condition is that column D matches cell A1 (text),
the second condition is that column K is greater than 0.
I need it to count the number of rows that meet the given conditions.
Please let me know if you can help
ROSE2102's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9202
View this thread: http://www.excelforum.com/showthread.php?threadid=507833
Hi, Rose. Try this:
http://www.officear...Conditional Formatting & CountIf problem
I've been trying to solve this problem for quite some time now but I
can't get a straight answer. I need to create a macro which will match
numbers from two lists and highlight the numbers on the master list
when the macro is ran. It should look work something like this:
formula is=CountIf($A$5:$A$4500 'from all grouped sheets',A5) =1
I don't know how I would program this into a VBA macro. The main idea
is A5:A4500 from all grouped sheets is compared to a5:a4500 on the
master sheet. Also, not all cells would be filled so I would need to
omit...CountIF problem #8
My brain isn't switched on this morning, this may be more straight
forward than I think...
I'm trying to do a Countif function, which only counts those in the
range which are between "0" and "-2". Lets say the range is $A$1 :
$A$50, and contains values between -3.00 and 5.00.
How can i write this countif formula? It doesnt work the way i'm trying
Many thanks in advance
jjj's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7424