Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Silver Lounger
    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
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    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")

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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 non-empty cells, not just text.

    Steve

  4. #4
    Silver Lounger
    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

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

  6. #6
    Plutonium Lounger
    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 non-blank cells. COUNTIF(range, condition) counts all cells within range satisfying the condition.

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

  8. #8
    Plutonium Lounger
    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.
    Attached Files Attached Files

  9. #9
    Silver Lounger
    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

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

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

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

  13. #13
    Plutonium Lounger
    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 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. #14
    Silver Lounger
    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
    Attached Files Attached Files

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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>

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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