Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. Senior Member
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. Super Moderator
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. Senior Member
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. I agreed to these rules
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. ## Re: Countif With Specialcells?

Hello amdas,

Welcome to Ozgrid.

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

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

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