#### Counting question #2

I have a range of cells that I want to 'count' if the number is greater than
0 but less than 6.  The cell # is F33 where I want the answer.  The range is:
Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.

What formula would I use?  I've tried several but I keep getting error
 0
Carl (99)
3/3/2005 8:37:08 PM
excel.misc 78881 articles. 5 followers.

11 Replies
769 Views

Similar Articles

[PageSpeed] 47

hi,
=count(Q13:W13,Q17:W17,Q21:W17,Q25:W25,Q29:W29)

>-----Original Message-----
>I have a range of cells that I want to 'count' if the
number is greater than
>0 but less than 6.  The cell # is F33 where I want the
>Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
>
>What formula would I use?  I've tried several but I keep
getting error
>.
>
 0
anonymous (74722)
3/3/2005 8:51:34 PM
It didn't work.  It totaled 35 (total number of cells) and not the number of
numbers above 0 but below 6.

"anonymous@discussions.microsoft.com" wrote:

> hi,
> =count(Q13:W13,Q17:W17,Q21:W17,Q25:W25,Q29:W29)
>
> >-----Original Message-----
> >I have a range of cells that I want to 'count' if the
> number is greater than
> >0 but less than 6.  The cell # is F33 where I want the
> >Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
> >
> >What formula would I use?  I've tried several but I keep
> getting error
> >.
> >
>
 0
Carl (99)
3/3/2005 9:09:10 PM
Kind of long, but this will work:

=-SUM(-COUNT
(Q13:W13,Q17:W17,Q21:W21,Q25:V25,Q29:W29),COUNTIF(Q13:W13,
{"<=0",">=6"}),COUNTIF(Q17:W17,{"<=0",">=6"}),COUNTIF
(Q21:W21,{"<=0",">=6"}),COUNTIF(Q25:W25,
{"<=0",">=6"}),COUNTIF(Q29:W29,{"<=0",">=6"}))

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I have a range of cells that I want to 'count' if the
number is greater than
>0 but less than 6.  The cell # is F33 where I want the
>Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
>
>What formula would I use?  I've tried several but I keep
getting error
>.
>
 0
jasonjmorin (551)
3/3/2005 9:36:35 PM
Careful. I have Q25:V25 in the 1st part of my formula
(?). Change that to:

Q25:W25

Jason

>-----Original Message-----
>Kind of long, but this will work:
>
>=-SUM(-COUNT
>(Q13:W13,Q17:W17,Q21:W21,Q25:V25,Q29:W29),COUNTIF
(Q13:W13,
>{"<=0",">=6"}),COUNTIF(Q17:W17,{"<=0",">=6"}),COUNTIF
>(Q21:W21,{"<=0",">=6"}),COUNTIF(Q25:W25,
>{"<=0",">=6"}),COUNTIF(Q29:W29,{"<=0",">=6"}))
>
>HTH
>Jason
>Atlanta, GA
>
>>-----Original Message-----
>>I have a range of cells that I want to 'count' if the
>number is greater than
>>0 but less than 6.  The cell # is F33 where I want the
>>Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
>>
>>What formula would I use?  I've tried several but I
keep
>getting error
>>.
>>
>.
>
 0
anonymous (74722)
3/3/2005 9:45:24 PM
A little shorter than my 1st formula:

=SUM(-COUNTIF(INDIRECT("Q"&{13,17,21,25,29}&":"&"W"&
{13,17,21,25,29}),{"<=0";">=6"}),COUNT
(Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29))

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I have a range of cells that I want to 'count' if the
number is greater than
>0 but less than 6.  The cell # is F33 where I want the
>Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
>
>What formula would I use?  I've tried several but I keep
getting error
>.
>
 0
jasonjmorin (551)
3/3/2005 9:48:38 PM
Another way...

=SUMPRODUCT(--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29
)>0),--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29)<6))

Hope this helps!

In article <4409F2B3-2D4A-439F-8B73-6EC223728B83@microsoft.com>,
"Carl" <Carl@discussions.microsoft.com> wrote:

> I have a range of cells that I want to 'count' if the number is greater than
> 0 but less than 6.  The cell # is F33 where I want the answer.  The range is:
> Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
>
> What formula would I use?  I've tried several but I keep getting error
 0
domenic22 (716)
3/3/2005 10:33:22 PM
I tried this formula and got an error message.

"Jason Morin" wrote:

> A little shorter than my 1st formula:
>
> =SUM(-COUNTIF(INDIRECT("Q"&{13,17,21,25,29}&":"&"W"&
> {13,17,21,25,29}),{"<=0";">=6"}),COUNT
> (Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29))
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >I have a range of cells that I want to 'count' if the
> number is greater than
> >0 but less than 6.  The cell # is F33 where I want the
> >Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
> >
> >What formula would I use?  I've tried several but I keep
> getting error
> >.
> >
>
 0
Carl (99)
3/11/2005 7:49:02 PM
I tried this and got an error message.

"Domenic" wrote:

> Another way...
>
> =SUMPRODUCT(--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29
> )>0),--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29)<6))
>
> Hope this helps!
>
> In article <4409F2B3-2D4A-439F-8B73-6EC223728B83@microsoft.com>,
>  "Carl" <Carl@discussions.microsoft.com> wrote:
>
> > I have a range of cells that I want to 'count' if the number is greater than
> > 0 but less than 6.  The cell # is F33 where I want the answer.  The range is:
> > Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
> >
> > What formula would I use?  I've tried several but I keep getting error
>
 0
Carl (99)
3/11/2005 7:51:02 PM
Can you post the exact formula you're using?

In article <7AC8E373-FC1B-427A-898A-903E7290A78A@microsoft.com>,
"Carl" <Carl@discussions.microsoft.com> wrote:

> I tried this and got an error message.
>
> "Domenic" wrote:
>
> > Another way...
> >
> > =SUMPRODUCT(--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29
> > )>0),--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29)<6))
> >
> > Hope this helps!
> >
> > In article <4409F2B3-2D4A-439F-8B73-6EC223728B83@microsoft.com>,
> >  "Carl" <Carl@discussions.microsoft.com> wrote:
> >
> > > I have a range of cells that I want to 'count' if the number is greater
> > > than
> > > 0 but less than 6.  The cell # is F33 where I want the answer.  The range
> > > is:
> > > Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
> > >
> > > What formula would I use?  I've tried several but I keep getting error
> >
 0
domenic22 (716)
3/11/2005 8:18:27 PM
I tried both formulas that Jason gave me and the one you gave me.

=-SUM(-COUNT
(Q13:W13,Q17:W17,Q21:W21,Q25:V25,Q29:W29),COUNTIF(Q13:W13,
{"<=0",">=6"}),COUNTIF(Q17:W17,{"<=0",">=6"}),COUNTIF
(Q21:W21,{"<=0",">=6"}),COUNTIF(Q25:W25,
{"<=0",">=6"}),COUNTIF(Q29:W29,{"<=0",">=6"}))

"Domenic" wrote:

> Can you post the exact formula you're using?
>
> In article <7AC8E373-FC1B-427A-898A-903E7290A78A@microsoft.com>,
>  "Carl" <Carl@discussions.microsoft.com> wrote:
>
> > I tried this and got an error message.
> >
> > "Domenic" wrote:
> >
> > > Another way...
> > >
> > > =SUMPRODUCT(--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29
> > > )>0),--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29)<6))
> > >
> > > Hope this helps!
> > >
> > > In article <4409F2B3-2D4A-439F-8B73-6EC223728B83@microsoft.com>,
> > >  "Carl" <Carl@discussions.microsoft.com> wrote:
> > >
> > > > I have a range of cells that I want to 'count' if the number is greater
> > > > than
> > > > 0 but less than 6.  The cell # is F33 where I want the answer.  The range
> > > > is:
> > > > Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
> > > >
> > > > What formula would I use?  I've tried several but I keep getting error
> > >
>
 0
Carl (99)
3/11/2005 8:33:02 PM
If you copied and pasted the formula from the newsgroup and into your
in the formula.  If so, this would give you your error message.  In this
case, correct the formula accordingly.

Does this help?

In article <72176F9E-CA7C-4870-BBBC-F43928105AFD@microsoft.com>,
"Carl" <Carl@discussions.microsoft.com> wrote:

> I tried both formulas that Jason gave me and the one you gave me.
>
> =-SUM(-COUNT
> (Q13:W13,Q17:W17,Q21:W21,Q25:V25,Q29:W29),COUNTIF(Q13:W13,
> {"<=0",">=6"}),COUNTIF(Q17:W17,{"<=0",">=6"}),COUNTIF
> (Q21:W21,{"<=0",">=6"}),COUNTIF(Q25:W25,
> {"<=0",">=6"}),COUNTIF(Q29:W29,{"<=0",">=6"}))
>
>
> "Domenic" wrote:
>
> > Can you post the exact formula you're using?
> >
> > In article <7AC8E373-FC1B-427A-898A-903E7290A78A@microsoft.com>,
> >  "Carl" <Carl@discussions.microsoft.com> wrote:
> >
> > > I tried this and got an error message.
> > >
> > > "Domenic" wrote:
> > >
> > > > Another way...
> > > >
> > > > =SUMPRODUCT(--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W2
> > > > 9
> > > > )>0),--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29)<6))
> > > >
> > > > Hope this helps!
> > > >
> > > > In article <4409F2B3-2D4A-439F-8B73-6EC223728B83@microsoft.com>,
> > > >  "Carl" <Carl@discussions.microsoft.com> wrote:
> > > >
> > > > > I have a range of cells that I want to 'count' if the number is
> > > > > greater
> > > > > than
> > > > > 0 but less than 6.  The cell # is F33 where I want the answer.  The
> > > > > range
> > > > > is:
> > > > > Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
> > > > >
> > > > > What formula would I use?  I've tried several but I keep getting
> > > > > error
> > > > > answers.
> > > >
> >
 0
domenic22 (716)
3/11/2005 9:28:08 PM

Similar Artilces:

Sharing a Workbook #2
I'm hoping someone can help me with this. I designed a finance matri for the company I work for so that the phone reps are able to decid whether or not an applicant qualifies for a credit card. The spreadsheet has a bunch of macros, drop down lists, and such. I is important that I track every 'set' of information that is keyed in. Right now, I have a button called "Submit" and it takes the info tha was keyed in, and stores it in another 'hidden' sheet in the workbook. I have 20 copies of the workbook, one for each employee. The proble arises when I need to make...

Pivot
Hi, I have a pivot with Sales information. The data has multiple columns by various product category. I have added a count column next to each product column representing the dollar amount. Is there a way in Pivot to get a calculated field of Total amount / Count for each product to arrive at Average value per deal. Excel does not allow me to use Calculated Item to get the average number. Any suggestion is welcome. Regards Sandip. If you are unable to create the formula in the pivot table, have yo considered placing it in a row next to the table? Just a thought... -- SHMUNC ---------...

Trendline values #2
I used the function "LOGEST" to get an exponential fit of a series of numbers, which gives me a value of 1.0610. I also used "RSQ" function to get the R-square, which is 0.9442. Then I made a chart based on the same series of numbers and add trendline to the curve. I selected "Exponential" under "Type" and checked "Display equation on chart" and "Display R-Squared value on chart". The value shown on the chart is "y=3928.8e^0.0592x" and "R^2=0.9104". I expected to see "y=3928.8e^0.0610x" and "R^...

A question in need of an answer
I have put some VBA code into a workbook to enable the ladies in the office to back things up without having to remember more than "left-click the button marked 'Backup' ". I have come to realize this was a bit more than they can handle, so I'm rewriting the code to try to make it even easier. While doing so, I came across an idea that I need info on...is it possible, using vba, to create a new folder (say, naming it the date of the backup) for each time they make the backups? If so, any examples of how? I've posted this before doing much research, but will ...

Outlook 2003 email #2
i am new to the outlook software; currently beginning to use outlook 2003. i am receiving duplicate emails in my in-box; including all the emails i imported from my web based email, yahoo. how do i fix this problem? many thanks for any and every ones helpful guidance. happy new year, technot -- "the world will not solve it''s current problems with like-minded thinking." -Albert Einstein Not a techie here but I have had the same problem. I think that your Outlook profile is corrupt. You can create a new one and import the messages again from your backup. To create a ...

Counting occurences of codes in cells
I have a spreadsheet with various codes separated by periods, in a single cell. Like: C T C,R T,C,R R and so forth. It's easy to count the single codes but how do I cound the "C" when it's with another character? There must be a simple way, without writing code, to do this. Is there? Thanks! maybe... =countif(a1:a10,"*" & "c" & "*") or =countif(a1:a10,"*c*") or if x99 contained the letter c. =countif(a1:a10,"*" & x99 & "*") salgud wrote: > > I have a spreadsheet with various codes separa...

MS Money 2003 #2
I am trying to load MS Money on my PC running Win 2000. All I get is a menu bar that is minimized in the bottom of my screen. Nothing else. I have close virus check and zonealarm, but I can't get the software to install. ...

2 year Lifespan?---
I understand that MSMoney has some sort of built in lifespan of 2 years. What stops working after this date? Do I just buy the next version or is there a way round this? It is mainly the stuff related to online working which stops working, ie quote downloads and bank imports. -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny or http://money.mvps.org/faq for UK tips and fixes for MS Money. To send Microsoft your product wishes see http://money.mvps.org/wishes.aspx I do not respond to any unsolicited email r...

Exchange/Outlook 2003
In an effort to make sure we never reach the 16GB threshold for the SBS 2003 Exchange Info Store, I'm looking to setup a GPO to give everyone consistent AutoArchive settings as well as a common location for the PST file. I have already downloaded the Office Administrative Templates for Outlook 2003, so I already have found the settings I want to modify and have made the changes, I just haven't made them live on the domain yet. My thoughts to date: * create two GPOs, one for the PST location, one for the autoarchive settings themselves * set PST location to local drive, or common netw...

Exchange 2003 (SP2) Mailbox Manager Exclusion Question...
I am the admin for a Exchange Server 2003 (SP2) Enviroment and an running into a problem with the Mailbox Manager.. We currently purchased a 3rd party product to archive e-mail outside of the exchange server. Basically a user creates a folder called "Archive" under the root of their folder list in Outlook and then they can create whatever folder structure they like under the folder called "Archive". Any e-mail a user wants to keep over 120 days (the limit we have mail saved on the exchange server) they drag and drop into this archive folder and then the 3rd party pro...

XML TreeNode.Parent property when using 2.0 TreeView?
How do I get a TreeNode.Parent property when using the 2.0 TreeView control? When the data source is an XML file there may be redundant names in the tree. For example, when a child node with the value of 'name' is selected I don't have a clue how to get the name of its parent which may be 'person' or 'school.' <%= Clinton Gallagher "clintonG" <csgallagher@REMOVETHISTEXTmetromilwaukee.com> wrote in message news:%23erL6WOXFHA.3532@TK2MSFTNGP10.phx.gbl... > How do I get a TreeNode.Parent property when using the 2.0 TreeView > control? &...

CRM 1.2 unable to add users in OU but domain root OK.
Hello. I am having trouble adding users to CRM 1.2. If I try to add a CRM user from an OU in the domain with deployment manager or the web GUI it fails. If I add a user to CRM from the root of the domain the user is added fine to CRM. In our test environment I was able to add users to CRM that are in OU's in a test domain without any trouble. Any ideas? Thanks!! Mike ...

Moving Database Paths on a cluster (5.5) KB278464
Config: - Win2k Cluster (SP4) - NT4 domain - Exchange 5.5 (SP4) Requirement: - Need to move the PRIV and PUB IS to a new drive. The KB article KB278464 outlines the correct proceedure, but lists Exchange 5.5 and OS NT4 in the "Applies to" section at the end of the article. My question is, will this proceedure also work properly on the Win2k OS? -- Tim. I believe it should work... "Tim Munro" <Plasmamage@ColdMail.com> wrote in message news:ODC2QsvfEHA.3024@TK2MSFTNGP10.phx.gbl... > Config: > - Win2k Cluster (SP4) > - NT...

COUNTIF query #2
Hey all Hopefully a nice easy one.... We have a logging system, and the spreadsheet basically has the person name doing a call, what the call is and how long it took. This is use all day so loads of calls get logged by various people. Say column A is Persons name and Column B is time spent. I need calulation that checks how much time 'person 1', 'person 2', 'person 3 etc has spent on calls during the day and add's it all up. Please can someone show me how to do this, i have tried a COUNTIF bu cant quite get it to work. Many thanks in advance. Darren (digital_life...

WM_COPYDATA question
Can you send WM_COPYDATA messages asynchronously? For example, using postmessage() instead of sendmessage()? >Can you send WM_COPYDATA messages asynchronously? For example, using >postmessage() instead of sendmessage()? AFAIK you have to use SendMessage. Dave On Jul 1, 10:15=A0am, "Nick Schultz" <nick.schu...@flir.com> wrote: > Can you send WM_COPYDATA messages asynchronously? =A0For example, using > postmessage() instead of sendmessage()? The quick answer is "no": You must use SendMessage and cannot use PostMessage. The docs formerly include...

I was able to use CL.exe to compile and link .cpp or .c file in a command line. But all in a sudden, I can not do this any more, as I always got such an error message: LINK : fatal error LNK1104: cannot open file 'C:\Documents' I didn't do complicated compiling. e.g. I even tested with the "hello word" thing. cl hello.c where hello.c is just this: #include <stdio.h> main() { printf("hello world!\n"); } But again, this LINK : fatal error LNK1104: cannot open file 'C:\Documents' happens too! It seems something wrong with the path that CL....

Restoring and backup of Business Portal 2.5 feature pack
how can i backup and restore my business portal site(2.5 feature pack). The objective is to move it to a different location. I have tried using stsadm.exe tool but it is not successfull. Any help would be greatly appreciated. ...

Formula to find text and count it within an entire workbook
I have a workbook where there is nothing consistent or any named ranges. I need to make a summary sheet and count everytime I have the word cable in the entire workbook in column B. I have tried different variations of countif, lookups and find but have not come up with a combination that works. My restraint is that I can't have a macro for this workbook. I really need some help, example below. A B 1 Cable 2 FSR 3 FRK 4 Wind Meter 5 Laptop Thanks in advance, Carrie -- Message posted via http://www.officekb.com Hi, =COUNTIF(\$a\$1:\$H\$5000,"*Cable*&q...

Installing Money 2007 #2
I have Money 2003 on my computer and I just purchased Money 2007 Deluxe. Do I need to have the latest version and where can I find them so that i can use Money 2007. Everytime I try to install, I get a message telling me that I need the latest version or the Service Pack. Can someone tell me what I need to get this Deluxe installed. Thanks! In microsoft.public.money, JMacon wrote: >I have Money 2003 on my computer and I just purchased Money 2007 Deluxe. Do >I need to have the latest version and where can I find them so that i can use >Money 2007. Everytime I try to inst...

Calendar Appointments #2
I enter a new appointment and nothing shows up on the screen. Anything I add to try to schedule anything doesnt show up.. just a blank calendar. This is Outlook 2003. Anyone have any ideas? ...

Null Return on Count Function
I have the following Query: SELECT IIf(Count(*) Is Null,0,Count(*)) AS [No longer in Facility] FROM Residents GROUP BY Residents.[Last Status Date] HAVING (((Residents.[Last Status Date]) Between [Forms]![Open_PIP Report]![DateStart] And [Forms]![Open_PIP Report]![DateEnd])); The query is still returning a null value. Many of the other queries in my project require this to be at least a zero value to calculate properly. I have tried using the NZ function also. Perhaps I am using it incorrectly....any ideas? Hi - I don't quite understand your question. First, Count(*) never retu...

Opporunity Close Opportunity Question
The Close Opporunity form has a description field on it, which does not transfer either the Topic or Description field contents from the Opportunity form into it. When we enter information into that field, we have been unable to locate into which SQL table it goes into. Can anyone shed any light on this? Thanks! Shauna Found it after some searching in the ActivityBase table. Does this field get pulled into any of the Opporunity reports? I have looked at most of them and they appear to pull in the Topic and Description fields from the Opportunities General Tab, not the Closed Opport...

Lost File #2
I have accidently saved over a publisher file and lost all my work. I am aware that I have temp files that may have the data in it but I cannot access any of them. I get an error message saying it is locked or in use by another person but they are not. Can anyone help >-----Original Message----- >I have accidently saved over a publisher file and lost all my work. I am >aware that I have temp files that may have the data in it but I cannot access >any of them. I get an error message saying it is locked or in use by another >person but they are not. > >Can anyon...

Restore Issue #2
Hello All: Having a problem restoring a database backed up with NT backup. The error I keep getting is: Failed to find a database to restore to from the Microsoft Active Directory. Storage Group specified on the backup media is a12b3cfb-292f-41b7-aa41-7391f464400d. Database specified on backup media is Mailbox Store3 (EXCH-1), error is 0xc7fe1f42. The backup media is obviously seeing the name incorrectly. How can i correct this. Thanks What should the db name be? Have you tested backups before because the backup may be corrupt. Nue "JS" <no@none.com> wrote in...

Excel Conditional Formula Problem #2