#### Countif help!!!

```Thanks in advance for any help offered.  I have a table with GPA's and
need to calculate the different ranges.  I have the function for 3.0 an
above and less than 2.5.  I am having trouble figuring out the formul
to count how many students are in the range of 2.50-2.99.

I thought I could do this:

=countif(f2:f406, ">=2.5+<=2.99")

I get "0" as a result.

any help is appreciated
:

--
Message posted from http://www.ExcelForum.com

```
 0
7/29/2004 11:27:42 PM
excel.newusers 15348 articles. 2 followers.

6 Replies
536 Views

Similar Articles

[PageSpeed] 46

```Hi,

You can use Sumproduct...

=SUMPRODUCT(--(F2:F406>=2.5),--(F2:F406<=2.99))

Hope this helps

--
Message posted from http://www.ExcelForum.com

```
 0
7/29/2004 11:44:04 PM
```Hi Bellis!

Only one condition argument is allowed by COUNTIF and SUMIF but:

One way:

=COUNTIF(F2:F406, ">="&2.5)-COUNTIF(F2:F406,">"&2.99)

Another way:

=SUMPRODUCT(--((F2:F406)>=2.5),--((F2:F406)<=2.99))

The arguments are implicit IF statements that return TRUE or FALSE.
The -- is coercing those returns to 1 and 0. SUMPRODUCT is multiplying
the results together and adding the answers so that only if both
conditions are met will 1 be counted.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
"bellis >" <<bellis.1a6i85@excelforum-nospam.com> wrote in message
news:bellis.1a6i85@excelforum-nospam.com...
> Thanks in advance for any help offered.  I have a table with GPA's
> and I
> need to calculate the different ranges.  I have the function for 3.0
> and
> above and less than 2.5.  I am having trouble figuring out the
> formula
> to count how many students are in the range of 2.50-2.99.
>
> I thought I could do this:
>
> =countif(f2:f406, ">=2.5+<=2.99")
>
> I get "0" as a result.
>
> any help is appreciated
> :)
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

```
 0
njharker (1646)
7/30/2004 12:01:39 AM
```Hi Bellis
Try this
=SUMPRODUCT((F2:F406>2.5)*(F2:F406<2.99))

Regards
Michael
>-----Original Message-----
>Thanks in advance for any help offered.  I have a table
with GPA's and I
>need to calculate the different ranges.  I have the
function for 3.0 and
>above and less than 2.5.  I am having trouble figuring
out the formula
>to count how many students are in the range of 2.50-
2.99.
>
>I thought I could do this:
>
>=countif(f2:f406, ">=2.5+<=2.99")
>
>I get "0" as a result.
>
>any help is appreciated
>:)
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
```
 0
anonymous (74722)
7/30/2004 12:05:10 AM
```Hi Bellis..........

=COUNTIF(F2:F406,"<=2.99")-COUNTIF(F2:F406,"<2.5")

Vaya con Dios,
Chuck, CABGx3

"bellis >" <<bellis.1a6i85@excelforum-nospam.com> wrote in message
news:bellis.1a6i85@excelforum-nospam.com...
> Thanks in advance for any help offered.  I have a table with GPA's and I
> need to calculate the different ranges.  I have the function for 3.0 and
> above and less than 2.5.  I am having trouble figuring out the formula
> to count how many students are in the range of 2.50-2.99.
>
> I thought I could do this:
>
> =countif(f2:f406, ">=2.5+<=2.99")
>
> I get "0" as a result.
>
> any help is appreciated
> :)
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

```
 0
croberts (1377)
7/30/2004 12:11:59 AM
```THis one worked great!  Thanks Michael.

Michael wrote:
> *Hi Bellis
> Try this
> =SUMPRODUCT((F2:F406>2.5)*(F2:F406<2.99))
>
> Regards
> Michael
> >-----Original Message-----
> >Thanks in advance for any help offered.  I have a table
> with GPA's and I
> >need to calculate the different ranges.  I have the
> function for 3.0 and
> >above and less than 2.5.  I am having trouble figuring
> out the formula
> >to count how many students are in the range of 2.50-
> 2.99.
> >
> >I thought I could do this:
> >
> >=countif(f2:f406, ">=2.5+<=2.99")
> >
> >I get "0" as a result.
> >
> >any help is appreciated
> >:)
> >
> >
> >---
> >Message posted from http://www.ExcelForum.com/
> >
> >.
> >

--
Message posted from http://www.ExcelForum.com

```
 0
7/30/2004 6:18:55 PM
```It leaves out values that are either 2.5 or 2.99  (which appear should be
included)

Are you sure it worked great?

--
Regards,
Tom Ogilvy

"bellis >" <<bellis.1a7ylh@excelforum-nospam.com> wrote in message
news:bellis.1a7ylh@excelforum-nospam.com...
> THis one worked great!  Thanks Michael.
>
> Michael wrote:
> > *Hi Bellis
> > Try this
> > =SUMPRODUCT((F2:F406>2.5)*(F2:F406<2.99))
> >
> > Regards
> > Michael
> > >-----Original Message-----
> > >Thanks in advance for any help offered.  I have a table
> > with GPA's and I
> > >need to calculate the different ranges.  I have the
> > function for 3.0 and
> > >above and less than 2.5.  I am having trouble figuring
> > out the formula
> > >to count how many students are in the range of 2.50-
> > 2.99.
> > >
> > >I thought I could do this:
> > >
> > >=countif(f2:f406, ">=2.5+<=2.99")
> > >
> > >I get "0" as a result.
> > >
> > >any help is appreciated
> > >:)
> > >
> > >
> > >---
> > >Message posted from http://www.ExcelForum.com/
> > >
> > >.
> > > *
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

```
 0
twogilvy (1078)
8/1/2004 5:37:28 PM

Similar Artilces:

Thanks for all the help on Year-to-date query!
It's so great to know that there are folks out there that can take the time to help someone whose 78 year old brain is slowly turning to gorgonzola cheese! Again, thanX Mikie Francisco 9 @ ATT N E T ...

Line graph help!!!
Hi, I need help! I'm trying to create a line graph of two separate sets of data (each in the form of x,y) forming a graph with two different lines, one for each set of data. I really don't know much about Excel so any help would be greatly appreciated. Thank You!! Hi, Assuming that you want them as 2 separate lines the best way to set up the data is X Y1 Y2 A 2 5 B 1 3 .... Each adjacent, then highlight the whole set of data and click the Chart Wizard button on the toolbar. -- Thanks, Shane Devenshire "Stephen Colbert" wrote: &g...

Pls Help Me! Creating Chart
:mad: Hi there! How can I create / prepare this kind of chart, so call "Waterfal Chart" in Excel worksheet? (01 file attached) Thanks again! davidiew :confused +------------------------------------------------------------------- |Filename: chart123.doc |Download: http://www.excelforum.com/attachment.php?postid=3726 +------------------------------------------------------------------- -- davidtip ----------------------------------------------------------------------- davidtips's Profile: http://www.excelforum.com/member.php?ac...

Need one help
Dear Sir/Madam, I need some help that I am working in cousulting firm and we providing solution to clinet as web base and mail base. We are microsoft ceritfied partner and we using win2k3 ent. server on this server exchange2k3 and other is win2k3 with IMail. Till upto now we are proving clients mail solution on Imail base but now we are planing to charge more for mail solution and those are interested to pay then we give them exchange mail solution. Now I having one question in my mind is that in IMail I create diffrent diffrent domain for different web site according to clients. Now o...

I am trying to switch info from one XP machine running office 2000 to another XP machine running office 2000 and I have a ton of Macros. How do I successfully move these Macros in excel, so I don't have to recreate them? Hi if you have stored your macros in your personal.xls just copy thie file to your new machine -- Regards Frank Kabel Frankfurt, Germany Bob Garber wrote: > I am trying to switch info from one XP machine running > office 2000 to another XP machine running office 2000 and > I have a ton of Macros. How do I successfully move these > Macros in excel, so I...

IF function help
Hi, I need some help creating an IF function. I have a list of salar brackets e.g A 0 - 10,000 B 10,000 - 20,000 C 20,000 - 30,000 D 30,000 - 40,000 etc On a seperate spreadsheet i have different cells with peoples salarie in. In a seperate cell underneath their salary cells, i have an empt cell that needs to have the relevant letter in that shows which ban they fall into A,B,C,D etc. What i need is an IF function in the blank cell that will automaticall come up when a persons salary is typed in. E.g : - if i type in 24,000 into the salary cell, i want the blank cel to then come up with ...

Help with formula #18
Hi All: I am WAY over my head here, and you all have always been great help, so hopefully someone can help with this. I had someone write this for me to use as an index at the top of a spread sheet. It works beautifully . The problem is I would like to move this index to sheet 2 of the same spreadsheet and when I copy and paste it, it does'nt work. Is there anyone who can modify this to work as an index in sheet 2 and will search sheet 1 for the results? TIA Keith =IF(#REF!="","",IF(ISNA(MATCH(#REF!,\$H\$31:\$H\$65536,0)),"",HYPERLINK("#"&C...

Help with mutiple profiles / accounts
Hi there, I have multiple different pop3 accounts i would like to use from 1 profile, is this possible? Please help. I have 3 different pop 3 accounts which i have setup. I also have 3 different data folders that the mail from each account drops into, this works fine. However, how do I set it so that when I reply to an email from 'pop1' it uses the pop1 smtp server and account details and when I reply or create emails for 'pop2' it uses pop2 details (the same for pop3). This was possible in outlook express, in the email window, there was a simply a drop down from which...

Help needed with Money 2003 files
Hi there, I've been using money 2003 for years even when upgrading to new PC's as I had the original disk. I've just had to rebuild my hard drive and now cannot find the disk anywhere so cannot install Money. As you cannot now buy Money, I've tried downloading the trial version of Money so that I can access all the account details/balances but it says that they're not compatible with earlier versions of money. Is there anything that I can do to get these files open or have I lost all my records for the last 6 years? I'd really appreciate your assistance. Jayne EggHead...

Exchange Server Problem (I need help)
Hello to you all! The medical clinic I work at has a network. We all have our own email address. Mine, for example is lpeacock@elcardiologist.com. The problem is this. I can send out email as much as I want and they go where I need them to go. However, when anyone attempts to send any of us email, to our work, we don't get them. Now we had experianced some MAJOR problems recently in the past to where the actual IT lady had to set everything back up because things just went down. So our email accounts worked without any problems. But now, we can't recieve anything from an...

MS Office Activation Assistant,HELP!!!!
HELP!! Everytime I click MS Office-60 Day Trial it opens Activation Assistant for the 2007 MS Office suites, and it has 2 steps. Step 1 is to request a trial key online which I have but i have many trial keys. Not only that when I click on the Step 1 icon it gives me another trial key already typed in the OEM key display area and it unlocks the 2nd step which is _Now,_click_here_to_launch_MS_Office_and_to_enter_your_trial_key__ so I do but then it says "The Microsoft Office Activation Assistant was unable to launch your 2007 Microsoft Office release. Please re-enter your sele...

BACKGROUND: I have 42 full time employess at my organization and 121 devices (85 fat clients & 36 thin clients). I am purchasing a new Windows 2008 server, running Exchange Standard to replace an existing 2003 Exchange standard server. I have an additional Windows 2003 server, running terminal services. CURRENT LICENSES (FULL TIME EMPLOYEES): 45 Windows 2008 user CALS 45 Exchange 2010 user CALS 40 Windows 2008 TS user CALS QUESTION: We hire a variable number of extra part time staff throughout the year to do computer work. Some may use a thin clients and some may use a ...

POS screen messed up, need help fixing it
For some reason on one of our registers, the POS screen is missing the status screen at the top, the function key buttons at the bottom, and the customer POS buttons on the right. the only thing showing in the POS screen is the 'bill to', 'ship to', and 'shipping information' boxes at the top, and then the transaction grid. what happened, and how can I get this other stuff back? thank you, kevin kskinne wrote: > For some reason on one of our registers, the POS screen is missing the status > screen at the top, the function key buttons at the bottom, and th...

HELP!!! #3
First, I was unable to display my Outlook folder. I couldn't go to Detect and Repair because I couldn't open Outlook. I proceeded to scanpst.exe to repair the problem. Now I've lost all my email messages. Did I lose everything? How do I restore the messages? I checked the box to save it in a file but where is that file? Please help quickly. I run a small business out of my home and need to find this info fast. ...

Macro to Delete Rows if true HELP!
I got this code from another answer and it works ok when I call it from the activesheet ("Agent Tenure") but I need to call it from another sheet named "Terminations"... please help???? Sub UPDATE_TENURE() Dim myrange, MyRange1 As Range lastrow = Cells(Rows.Count, "N").End(xlUp).Row Set myrange = Sheets("Agent Tenure").Range("N1:N" & lastrow) For Each c In myrange If UCase(c.Value) = "TRUE" Then If MyRange1 Is Nothing Then Set MyRange1 = c.EntireRow Else Set MyRange1 = Uni...

Help Program Bug
When the money help program starts I receive the following error pop up: "A Runtime Error as occured, Do you wish to Debug", Line 7, Error: Expected ";" Never Mind, I found the bug. My money file name had a apostrophe in it. Apparently Microsoft did not take this into consideration. I simply removed the apostrophe and all is better. "JohnC" wrote: > When the money help program starts I receive the following error pop up: > "A Runtime Error as occured, Do you wish to Debug", Line 7, Error: Expected > ";" ...

HELP: CRM mail doesn't always register
When we send an e-mail via CRM, it gets logged fine within the lead/opportunity/account. However when we receive a reply to a message, if we delete it within a few minutes of getting it, it doesn't register in CRM. Why is this? Thanks ...

Still need help
Here's my last dialogue. I'm still unable to clear a cell upon Close Thanks FSt1 ! I'm a novice and need further clarification. The subroutine did not run as I received the Macro Disabled message. I clicked OK, and next time I opened the workbook, I didn't get the Macro Warning, but the subroutine apparently didn't run as the chosen cells were not blank. I don't want people who use my workbook to have to deal with macro warnings. Is this something that must be set on each user's computer, or can I set it in my workbook so the subroutine automati...

Meeting Requests Help
We have a user here at work that wants to send out multiple day meeting requests at different times through 1 email request , is this possible , or is there a way to make this possible through a script. Thanks The user can create the items, insert them into an email with instructions to click on each one (if all are required attendance) or click on one if it is a multiple choice of single event times. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furio...

help me with this forumla
=AND(\$D\$3="Y",EXACT(\$D\$3,UPPER(\$D\$3))) I would like to be able to put a "y" in any cell in column "D" and have the same cell in column "E" turn blue can you help me set this up thanks jo -- Message posted from http://www.ExcelForum.com Joe, this simpler formula worked fine for me: =OR(D1="y",D1="Y") I selected Column E (just click the column heading), then Format > Conditional Formatting. Formula is: =OR(D1="y",D1="Y"). Then I set up the format. -- DDM "DDM's Microsoft Office Tips and T...