Countif and VBA

countingapples

countingapples

Board Regular
Joined
Feb 20, 2003
Messages
243
Can I use: {=sum(1/countif(data,data))} in the following code? If so, how do I do so?

Code: 
With ActiveSheet.PivotTables("Unit Response Summary").PivotFields( _
        "Incident Type Class")
        .Orientation = xlDataField
        .Position = 1
    End With
ActiveSheet.PivotTables("Unit Response Summary").PivotFields("Count of Incident Type Class"). _
        Caption = "Count"

I need to count each instance once. Right now it is returning the counts for every instance. If there are 9 line items for one unique key, I don't want it to return 9, I want it to return 1.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
you can use

application.worksheetfunction.sum

and

application.worksheetfunction.countif

in your vba code.
 
Upvote 0
Hard not to when you are struggling with something that you just can't seem to get ahold of after such a long time.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA/Macro: Adding Pivot table with connection string
Replies
1
Views
300
Jette3173
J
S
  • Question Question
Macro for
Replies
2
Views
136
Sanderb
S
D
  • Question Question
VBA Coding for adding Pivot Table with changing data table
Replies
6
Views
330
darmst24
D
A
  • Solved
COUNT multiple SUM COUNTIFS
Replies
6
Views
394
Peter_SSs
Peter_SSs
C
  • Solved
VBA Add Pivot Table Sheet Name
Replies
1
Views
198
Anthony47
A

Forum statistics

Threads
1,225,138
Messages
6,183,089
Members
453,147
Latest member
Bree2019

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top