Results 1 to 15 of 19
Thread: Count formula not working (XP)

20070205, 02:12 #1
 Join Date
 Jan 2002
 Posts
 1,615
 Thanks
 0
 Thanked 1 Time in 1 Post
Count formula not working (XP)
Hi,
I have a spreadsheet that is used to enter data on patients. I need to obtain counts of information but cannot get the formula to work. In column A I'm trying to count the number of names. In column B I need to count the number of scores >3. In column C I need to count the number of 1 and the number of 1 answers. I've left the formulas without the = sign so you will see what I've tried.
Thanks!
Leesha

20070205, 02:32 #2
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Count formula not working (XP)
COUNT counts numbers COUNTA counts text.
For your A column :
=COUNTA(A7:A11)
For the rest of the columns, take a look at the COUNTIF funtion.
Your B column would be:
=COUNTIF(B7:B11,">3")
C column:
=COUNTIF(C7:C11,"=1")
=COUNTIF(C7:C11,"=1")

20070205, 12:42 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Count formula not working (XP)
<img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>COUNTA counts nonempty cells, not just text.
Steve

20070205, 13:15 #4
 Join Date
 Jan 2002
 Posts
 1,615
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Count formula not working (XP)
Thanks to both of you! I especially appreciate the rationale behind which "count" to use.
Leesha

20070205, 13:41 #5
 Join Date
 Jan 2002
 Posts
 1,615
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Count formula not working (XP)
OK, here's another glitch. I've added a column to the spreadsheet that provides the month. If I want to count the text in a row but only for a certain month, how do I specifiy that? This is what I tried but it still returns the full count of the text items and doesnt' filter out the month.
=COUNTA('PAIN DATABASE'!B2:B26,"November")
Thanks,
Leesha

20070205, 13:44 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Count formula not working (XP)
If you want to count cells satisfying a condition, use COUNTIF instead of COUNTA.
COUNTA simply counts nonblank cells. COUNTIF(range, condition) counts all cells within range satisfying the condition.

20070205, 14:56 #7
 Join Date
 Jan 2002
 Posts
 1,615
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Count formula not working (XP)
Thanks Hans! That worked beautifully. One more question. I'm trying to make this worksheet user friendly for the end user. They presently use the autofilter to find the records by a particular staff member, The calulations for all of the columns however stay the same and do not recalculate based on staff member. The staff member names change thoughout the year so its virtually impossible to preset up calculations based on individual employee names. Is it even possible to do calculations using the autofilter?
Thanks!
Leesha

20070205, 15:12 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Count formula not working (XP)
You can use SUBTOTAL formulas  they will return results based only on the visible rows. Look up SUBTOTAL in the Excel help to see which functions are available.
It's not clear to me where in the spreadsheet you attached the staff member is specified, so I have attached a sample file with nonsense data.

20070205, 15:14 #9
 Join Date
 Jan 2002
 Posts
 1,615
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Count formula not working (XP)
Thanks for pointing me in the right direction and for the sample!!
Leesha

20070205, 19:33 #10
 Join Date
 Jan 2002
 Posts
 1,615
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Count formula not working (XP)
Back again! I'm having a great deal of fun with all the subtotal options. My problem now is that I don't see how to use the countif function with the subtotal function. Is it possible?
Thanks,
Leesha

20070205, 19:41 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Count formula not working (XP)
The SUBTOTAL function will summarize the filtered data. What do you want to use COUNTIF for in addition to SUBTOTAL?

20070205, 20:15 #12
 Join Date
 Jan 2002
 Posts
 1,615
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Count formula not working (XP)
When I use the SUBTOTAL function it is working fine. However there is a column of data thta I need to get the count of the number of times the value is greater than 3 for the filtered items. For example, before using the SUBTOTAL function the formula was =COUNTIF(D2:d26,">3"). It would return the number to me correctly. I didn't see how to use that with SUBTOTAL and when I put the formula in the cell like above the value doesn't change for filtered data, unless I was doing something wrong. Which we both know is highly likely!
Leesha

20070205, 20:43 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Count formula not working (XP)
The easiest way to do this would be to filter the records for which the relevant column is greater than 3. The SUBTOTAL function that counts will then automatically display the correct count.
There is no builtin function that returns a count or sum of filtered records that satisfy an extra condition, but you could modify the functions provided by <!profile=sdckapr>sdckapr<!/profile> in <post:=302,082>post 302,082</post:> to take an extra argument.

20070205, 22:21 #14
 Join Date
 Jan 2002
 Posts
 1,615
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Count formula not working (XP)
Hi,
Unfortunately it won't work doing the filter on the column that has items that I'm looking for the count >3. The filters will be on the month, year or employee and will have graphs associated with this data to show the items greater than 3 per employee per month etc.
I took a look at the link you provided and copied the code into a module on sheet1 as that is what I understood I was to do and then tried the forumula but I get an error that says ?Name no matter how many ways I try it. I'm attaching the spreadsheet as I'm not sure what I'm doing wrong and/or if I was supposed to edit the module code  if so OMG!
Thanks,
Leesha

20070205, 22:39 #15
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,290
 Thanks
 3
 Thanked 197 Times in 183 Posts
Re: Count formula not working (XP)
You can do this using SUMPRODUCT. Let's say the data you are interested in is in B7:B50; to get the count of filtered rows greater than 3, you would use:
<code>=SUMPRODUCT(($B$7:$B$50>3)*SUBTOTAL(3,OFFSET ($B$7:$B$50,ROW($B$7:$B$50)MIN(ROW($B$7:$B$50)),,1)))</code>
HTHRegards,
Rory
Microsoft MVP  Excel