Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Countif With Specialcells?

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Countif With Specialcells?

    I have a list of data, filtered. I need to sum the filtered list...no problem...but I also need to count the number of claims that make up the sum. Problem here is the filtered list may contain claims with zero liability...in which case I do not want to count it as a claim.

    To sum I'm using this code:
    Code:
                Value = WorksheetFunction.Sum(Columns(8).SpecialCells(xlCellTypeVisible))
    And I tried using this code to count only the filtered rows that have a value greater than 0:
    Code:
                   
                bytclaims = WorksheetFunction.Countif(Columns(8).SpecialCells(xlCellTypeVisible),">0")
    Does anyone have a solution? I'm in a pinch, as I just noticed this & I have to have it ready for tomorrow.

    Thank you for the time...

  • #2
    Re: Countif With Specialcells?

    Hi,

    Dim vcRng   As String

    vcRng = Range("h1", Range("h" & Rows.Count).End(xlUp)).Address(0, 0)
    bytclaims = Evaluate("sumproduct(subtotal(3,offset(" & vcRng & ",row(" & vcRng & _
    ")-row(h1),,1)),--(" & vcRng & ">0))")
    MsgBox bytclaims


    HTH
    Kris

    ExcelFox

    Comment


    • #3
      Re: Countif With Specialcells?

      Yes, that helped a lot. Thank you very much.

      ...I'll figure out how it works, tomorrow once I'm all done.

      Comment


      • #4
        Re: Countif With Specialcells?

        Code:
        "Dim vcRng   As String
        vcRng = Range("h1", Range("h" & Rows.Count).End(xlUp)).Address(0, 0)
        bytclaims = Evaluate("sumproduct(subtotal(3,offset(" & vcRng & ",row(" & vcRng & _  ")-row(h1),,1)),--(" & vcRng & ">0))")
        MsgBox bytclaims "
        Hi: How can i sum word instead of a number For example: -(" & vcRng & " =TEST))")
        Last edited by AAE; January 27th, 2013, 22:30. Reason: reformat code, add code tags

        Comment


        • #5
          Re: Countif With Specialcells?

          Hello amdas,

          Welcome to Ozgrid.
          Glad tohave you here, however, please read the forum rules.

          Do not post your question in threads started by others - - this is known as thread hijacking.
          Always start a new thread for *YOUR* question. If you find it helpful to clarify your needs you can include a link to this ( or any other ) thread.

          When starting a new thread be sure to give it a search-friendly title that aptly describes your need.

          Also,

          All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.
          I've added the tags for you this time only. Be sure to use them in future posts.

          How to use code tags

          [code]
          your code goes between these tags
          [/code]

          Or, just highlight all of the code and press the # button to add the code tags
          AAE
          ----------------------------------------------------

          Forum Rules | Message to Cross Posters | How to use Tags

          Comment

          Trending

          Collapse

          There are no results that meet this criteria.

          Working...
          X