Count formula not working (XP)

[Leesha]

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

[mbarron]

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")

[sdckapr]

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

Steve

[Leesha]

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

Leesha

[Leesha]

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

[HansV]

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.

[Leesha]

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

[HansV]

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.

[Leesha]

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

[Leesha]

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

[HansV]

The SUBTOTAL function will summarize the filtered data. What do you want to use COUNTIF for in addition to SUBTOTAL?

[Leesha]

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

[HansV]

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.

[Leesha]

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

[rory]

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