# Thread: Count formula not working (XP)

1. ## 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

2. ## Re: Count formula not working (XP)

COUNT counts numbers COUNTA counts text.
=COUNTA(A7:A11)

For the rest of the columns, take a look at the COUNTIF funtion.
=COUNTIF(B7:B11,">3")

C column:
=COUNTIF(C7:C11,"=-1")
=COUNTIF(C7:C11,"=1")

3. ## Re: Count formula not working (XP)

<img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>COUNTA counts non-empty cells, not just text.

Steve

4. ## Re: Count formula not working (XP)

Thanks to both of you! I especially appreciate the rationale behind which "count" to use.

Leesha

5. ## 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

6. ## Re: Count formula not working (XP)

If you want to count cells satisfying a condition, use COUNTIF instead of COUNTA.

COUNTA simply counts non-blank cells. COUNTIF(range, condition) counts all cells within range satisfying the condition.

7. ## 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

8. ## 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.

9. ## Re: Count formula not working (XP)

Thanks for pointing me in the right direction and for the sample!!
Leesha

10. ## 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

11. ## 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?

12. ## 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

13. ## 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 built-in 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.

14. ## 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

15. ## 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>

HTH

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•