Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Countif With Specialcells?

  1. #1
    Join Date
    10th September 2004
    Posts
    115

    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:
    VB:
    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:
    VB:
     
    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...

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Countif With Specialcells?

    Hi,

    VB:
    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

  3. #3
    Join Date
    10th September 2004
    Posts
    115

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    25th November 2012
    Posts
    11

    Re: Countif With Specialcells?

    VB:
    "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 at 22:30. Reason: reformat code, add code tags

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,229

    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. End(xlUp) vs SpecialCells(xlCellTypeLastCell)
    By ShosMeister in forum Excel General
    Replies: 5
    Last Post: August 8th, 2006, 19:41
  2. SpecialCells-xlconstants
    By JMAN in forum Excel General
    Replies: 2
    Last Post: August 5th, 2006, 12:44
  3. .SpecialCells(xlSameFormatConditions)
    By spog00 in forum Excel General
    Replies: 2
    Last Post: July 13th, 2005, 18:11
  4. Need help on using specialcells instead of a do loop?
    By dhansma in forum Excel General
    Replies: 2
    Last Post: February 16th, 2005, 08:13
  5. Use of Selection.SpecialCells(xlCellTypeConstants, 23)
    By pierrelogic in forum Excel General
    Replies: 13
    Last Post: July 24th, 2004, 00:29

Bookmarks

Posting Permissions

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