#### IF, COUNTIF

```Can someone explain why these two expressions differ referring to the value 5?

IF(A2:A10<5  etc.  )  but
COUNTIF(A2:A10,"<5")  requires a comma and quotes

Wouldn't it make sense for them to be the same?

Just wondering.
``` 0  Harvey
2/6/2010 3:28:53 AM mac.office.excel  1146 articles. 0 followers. 2 Replies 1356 Views Similar Articles

[PageSpeed] 31

```For us it would seem to make sense, but for the computer not so much :-)

My understanding is that in the IF statement the <5 is a Comparison
Operation which is a part of the 1st Function Argument whereas in the
COUNTIF it is a Criteria Reference supplied as the 2nd independent Function
Argument. If it stood alone as simply <5 it would have no logical meaning.

Regards |:>)
Bob Jones
[MVP] Office:Mac

On 2/5/10 10:28 PM, in article
hnwaxman-280F83.22285305022010@news.microsoft.com, "Harvey Waxman"
<hnwaxman@SgPmAaMil.com> wrote:

> Can someone explain why these two expressions differ referring to the value 5?
>
> IF(A2:A10<5  etc.  )  but
> COUNTIF(A2:A10,"<5")  requires a comma and quotes
>
> Wouldn't it make sense for them to be the same?
>
> Just wondering.

``` 0  CyberTaz
2/6/2010 3:24:41 PM
```Let's vote on it
:-)
Thanks

In article <C792F3F2.5AE9E%onlygeneraltaz1@com.cast.net>,
CyberTaz <onlygeneraltaz1@com.cast.net> wrote:

> For us it would seem to make sense, but for the computer not so much :-)
>
> My understanding is that in the IF statement the <5 is a Comparison
> Operation which is a part of the 1st Function Argument whereas in the
> COUNTIF it is a Criteria Reference supplied as the 2nd independent Function
> Argument. If it stood alone as simply <5 it would have no logical meaning.
>
> Regards |:>)
> Bob Jones
> [MVP] Office:Mac
>
>
>
> On 2/5/10 10:28 PM, in article
> hnwaxman-280F83.22285305022010@news.microsoft.com, "Harvey Waxman"
> <hnwaxman@SgPmAaMil.com> wrote:
>
> > Can someone explain why these two expressions differ referring to the value 5?
> >
> > IF(A2:A10<5  etc.  )  but
> > COUNTIF(A2:A10,"<5")  requires a comma and quotes
> >
> > Wouldn't it make sense for them to be the same?
> >
> > Just wondering.
``` 0  Harvey
2/6/2010 3:31:37 PM
 Reply: Similar Artilces:

CountIf Statement
Can anybody help? I am creating a formula in a cell of a spreadsheet which will Count all in stances of "distribution centre" within a column This is achieved using the =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") I was wondering whether it was possible in excel to use a statemen like =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") and (nex expression) any ideas -- Message posted from http://www.ExcelForum.com You can use sumproduct to count with more than one condition multiple ranges =SUMPRODUCT(--(Range1="GROUP&...

IF, COUNTIF
Can someone explain why these two expressions differ referring to the value 5? IF(A2:A10<5 etc. ) but COUNTIF(A2:A10,"<5") requires a comma and quotes Wouldn't it make sense for them to be the same? Just wondering. For us it would seem to make sense, but for the computer not so much :-) My understanding is that in the IF statement the <5 is a Comparison Operation which is a part of the 1st Function Argument whereas in the COUNTIF it is a Criteria Reference supplied as the 2nd independent Function Argument. If it stood alone as simply <5 it would ...

Countif ...
I have a column with phone numbers. I want to count how many phone numbers have the area code "214" and "972". Thanks, the first formula worked. "Jason Morin" wrote: > The formula depends on how your phone numbers are formatted and whether they > are text or actual 10 digit numbers. For example, if they are text and you > simply need the first 3 numbers in the cell, try: > > =SUMPRODUCT(--(LEFT(A1:A10,3)={"214","972"})) > > If the area codes are enclosed in parentheses, you could use: > > =SUM(COUNTIF(A1:A10...

Criteria/CountIf and Pivot Table
Hello: My data looks like this ID Date CWA Amount 1 08/12/2006 0 \$0.00 2 08/13/2006 1 \$10.00 3 08/14/2006 1 \$20.00 4 08/01/2006 0 \$0.00 Based on the above data, I want to create a pivot, by month (I know I only have Aug here) to show the following: Sum of CWA Count of CWA Percent of cases that were submitted with CWA. The CWA is an indicator field that looks at another field (not listed here) to determine if there is cash in a...

Countif
I am trying to solve the following problem i) cells A1 to A10 have either M or F (male or female) ii) cells B1 to B10 have either (grades) A B or C iii) I want (eg cell B13) to state the number of female students who scored A... and cell B14; how many female students who gained B etc I have tried various things including countif, sumif etc. It seems like a straight forward problem but the solution evades me! Eddie =SUMPRODUCT((A1:A10="F")*(B1:B10=LEFT(ADDRESS(ROW(B1),ROW(A1),4),1))) in Cell B13 and filled down to Cell B15. Alan Beban Eddie wrote: > I am trying to solve ...

CountIf with And
Novice Excel user on Excel 2003. I have a yearly data input sheet to track codes. I use the date (fomatted as 1/01/2009) and a code. I want to track the codes in a mothly chart for ease in summarization and improvment tracking. The codes are used to define a department and an error in that department (code 1a means - AR department and error a). I need to pull and total the amount for each code for each month. After searching for a while it seems that using CountIf with And does not work. Any suggestions would be greatly appreciated. -- Mike =SUMPRODUCT(--(MONTH(date_range)=4),...

Excel countif and
is there a "and" condition if you use countif or sumif? example; 1 c 1 1 c 2 2 < countif(b1:b4,"=c")and(a1:a4,"=1") Hi! Try this: =SUMPRODUCT(--(A1:A4=1),--(B1:B4="C")) Biff "KEN" <KEN@discussions.microsoft.com> wrote in message news:02a801c54ae1\$3371a4f0\$a401280a@phx.gbl... > is there a "and" condition if you use countif or sumif? > example; > > > 1 c > 1 > 1 c > 2 > 2 < countif(b1:b4,"=c")and(a1:a4,"=1") > ...

Countif() formula
Hi I have a column in Excel, D7:D200, filled of real numbers. Also the cell F3 includes a number (variable cell). I would like to count how many numbers of the column D7:D200 are >F3, but in successive ranges of D column. (That means how many numbers in: D7:D8 > F3, D7:D8 > F3, D7:D9 > F3…………….and finally D7:D200 > F3). To do that I have applied in E7 the formula: Countif(D7:\$D\$7 ; “>F3”) and expand down to E200. Unfortunately the above formula returns zero in all applied cells. If I change the F3 in the formula using a stable number (2,3,4 etc) the formula works, but...

CountIF by Background Color
Need a formula to count the cells in a range with a background color yellow (6). You can use this short UDF called CountYellow. Note that it only counts true background color, not format generated by conditional formatting. Detecting conditional formatting is quite a bit more complicated... Public Function CountYellow(r As Range) As Double Application.Volatile = True CountColor = 0 For Each c In r If c.Interior.ColorIndex = 6 Then CountColor = CountColor + 1 End If Next End Function -- Best Regards, Luke M *Remember to click "yes" if this post helped you...

Countif Formula with changing criteria
Hi. I'm trying to create a formula that will count how many numbers in a list of ten that are greater than a changing number in a cell. The cell changes as it has a formula attached to it. I used as the criteria >b1, but for some reason this doesn't work. As this number changes it has to be greater than whatever the cell value is at that time. -- Thanks! Stephen try =sumproduct(--(range>B1),1) "Stephen" wrote: > Hi. I'm trying to create a formula that will count how many numbers in a list > of ten that are greater than a changing number in a cell. T...

countif
I am trying to count a range of numbers in a column I created in excel to see how many times a particlar range appears. For example, I have a column with 10 rows that has a unique zip codes in each of the cells. 90001 90003 91005 90009 90207 94001 96007 97009 98009 98991 I want to know how many times each number below 94000 occurs and how many instances in which numbers greater than 94000 occur. What formula do I use. Thank you. cp Data in A1 to A10. Try this: =COUNTIF(A1:A10,">94000")&" above 94000 and "&COUNTIF(A1:A10,"<94000")&&quo...

IF COUNTIF
I am trying to get this to work =IF(COUNTIF(D83:S83,"=X"),"N","Y"),+IF(COUNTIF(L83,"=X"),"N","P") so that if L83 is selected the result in the cell would be P. Perhaps I am using the wrong function. Ideas anyone? Thank you. -- Mary Looks like the wrong function. When you say selected, what do you mean? It looks like you might just want =IF(L83="X","P","N") HTH Bob "Hmmmmm?" <Hmmmmm@discussions.microsoft.com> wrote in message news:DE1EDCE6-5F48-455A-B50E-185...

countif blanks
I need to count the number of blanks in a range. I'm trying countif(H1:H20,"<>""") but it's not working. Any ideas? =COUNTIF(H1:H20,"<>"&"") Or to account for the "dreaded SPACE BAR" =SUMPRODUCT((LEN(TRIM(H1:H20))>0)*1) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Radon" <Radon@discussions.microsoft.com> wrote in message news:0924636B-562F-4530-B972-FB7964F5F58C@microsoft.com... >I need to count the number of blanks in a range. I'm trying > countif(H1:...

Need help with CountIF function
Hello, Please help. I have a columne A with department codes A, B, C, ... the department codes are multiple lines, column B with numbers when there are activity in that department. Can I do a countif function or other function to summerize how many activity each department? To count how many times there is activity for dept A: =SUMPRODUCT(--(A2:A100="A"),--(ISNUMBER(B2:B100)) To sum the numbers in column B associated with dept A: =SUMIF(A:A,"A",B:B) -- Best Regards, Luke M "Flo" <Flo@discussions.microsoft.com> wrote in message...

COUNTIF Range
Does the COUNTIF range have to be a continuous range (i.e. A1:E1) or could it be a non-continuous range of cells (i.e. A1,D1,G1,J1,M1)? I want to count the number of occurances of a cell value being > 75 in the non-continuous range of A1,D1,G1,J1,M1. The COUNTIF function does not like the non-continuous range. Is there a better way of doing this. It is not an option to move the columns of data so they are a continuous range. Mike -- mluetkem ------------------------------------------------------------------------ mluetkem's Profile: http://www.excelforum.com/member.php?action=...

COUNTIF with or + and variables
I'm having a hard time figuring this one out My challenge is to count if column [I] is "A" or "B" and column [K] and [O] are "C" I'm trying to count all the ones in column [J] that meet the specific conditions. I tried an array but it didn't seem to take. Any hints are greatly appreciated. Try this… =COUNTIF(I:I,"A")+COUNTIF(I:I,"B")+COUNTIF(K:K,"C")+COUNTIF(O:O,"C") Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Dances...

Countif
I have 2 columns A & B with the following values: A B 1 4 1 2 5 2 3 6 3 How can I count the number of cells that A=3 with B="" using excel built-in functions? How can I combine the 2 criteria? Thanks A Lot!! Frank =sumproduct((A1:A10=3)*(B1:B10="")) "frank716" <frank716.t8ewn@excelforum.com> wrote in message news:frank716.t8ewn@excelforum.com... > I have 2 columns A & B with the following values: > > A B > 1 4 > 1 > 2 5 > 2 > 3 6 > 3 > > How can I count the number of cells that A=3 with B="&...

COUNTIFS with OR
Hi I have a sheet as below which is around 1000 rows long. Column A contains upto 100 different site codes. Column B always has 2 letters and there are 40 different combinations of the 2 letters possible. I am trying the following (which does not work obviously): COUNTIFS(A:A,1223,B:B,("Red" OR "Blue" OR etc etc)) A B 1 1223 Red 2 1223 Blue 3 1234 Blue 4 1223 Green 5 1235 Orange I know I could use COUNTIF+COUNTIF+.... multiple times but I would need 20 added together and want to simplify the formula. I am now losing the will to live looking for a...

Countif?
I'm looking for some help with the following; What I want to do in another cell is look up a range on column A and if its "text 1" count the number of "text 2" in column B. ie A1 B1 Text 1 Y Text 1 N Text 2 Y Text 2 Y This would result in For text 1 with Y = 1 For text 1 with N = 1 For text 2 with Y = 1 For text 2 with N = 0 Any help would be appreciated. Thanks, Teeb -- teeb ------------------------------------------------------------------------ teeb's Profile: http://www.excelforum.com/member.php?action=getinfo&...

SUMPRODUCT or COUNTIF??
All, I have been working on this for ages and can't get it right, and yet i feel it should be really simple! Can anyone help? I have an excel workbook which has 2 worksheets. On worksheet 1 (called 2005Stats) is a table: ColumnA ColumnB 01/01/2005 Placed 10/01/2005 Placed 15/01/2005 Open 16/02/2005 Closed 23/06/2005 Placed ColumnA has over 100 records with all different dates of this year, ColumnB has either "Placed", "Closed" or "Open" On worksheet 2, called 2005Calcs i want to have...

Countifs
Hi All, New in Excel 2007. Why is Countifs giving an error when I try to enter it with combined formulas like: =countifs(month(range);1;other_range;1) With kind regards, JP >=countifs(month(range);1;other_range;1) SUMIF SUMIFS COUNTIF COUNTIFS AVERAGEIF AVERAGEIFS These functions can only handle "straight" comparisons. That is, you can't manipulate a range array to test for a condition. In the formula above you're trying to manipulate the range array by first testing for the month. MONTH(range) = 1 The test has to be a "...

COUNTIF question
Hey all, i'm stuck. I need to do a formula that will count the number of cells in column 'A' only if the corresponding cell in column 'B' has a value of '0'. =COUNTIF(O:0,"0")+COUNTIF(P:P,">0") - just counts both, and that's as far as i have got. Can anyone help me please. Thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ This is probably a cheats way of doing it but... I would create a third column with...

Countif(and
I want to count cells in Column C when column A criteria are met and Column B criteria are met. ex. Countif(AND(A1:A5,M3,B1:B5,M4,COUNT(C1:C5) =SUMPRODUCT(--(A1:A5=M3),--(B1:B5=M4),--(C1:C5<>"")) -- David Biddulph "John" <John@discussions.microsoft.com> wrote in message news:EE92CF58-B41A-49F3-BE6E-00567F3DF568@microsoft.com... >I want to count cells in Column C when column A criteria are met and Column >B > criteria are met. > ex. Countif(AND(A1:A5,M3,B1:B5,M4,COUNT(C1:C5) John, It's not entirely clear what you mean ...

COUNTIF formula?
Hi, I=92m using Excel 2002 and I have a question about using a formula to determine if certain words are found within a cell. For example, I want to determine if several words are found in each cell in column A and, if so, I want to put the word =93Yes=94 in the corresponding cell in column B. If the word isn=92t found then I just want that cell in column B to remain blank. So, in the following example, I want to see if the following words: Oranges Apples Lemons are found in any of the cells in column A. The result would look like this: Column A Column B Apples, Oranges, Pears Yes ...

Sorting and COUNTIF
This may be a double post; apologies. This problem seems to happen only when my RANGE is on a different worksheet than my CRITERIA. When I run a COUNTIF, if I sort the table, the criteria returns the count of whatever is in the position of where the CURRENT criteria USED to be. I don't want that. I never know how to explain relative and absolute references, so let me give you an example. Sheet 2 has an inventory of four items: Hard-boiled eggs, oil filters, Jonas Brothers posters, and dry-erase markers. For simplicity, let's call it "Sheet 2! Range". I...