Countifs in VBA

[sathishkm]

Hi,

I have a table which is having company and title. Here I need to the countifs to check the repeated title for same company using countifs(). Is there a vba or macro to the same thing. Please find my attached sample file.

Regards
Sathish

 

[Chihiro]

Use Evaluate or Application.CountIfs(), or just use Range.Formula

That'll be the easiest method.

Otherwise, you'll need some sort of container and logic to hold Company & Title, and the count. Typically done using dictionary object.

As well, you should use absolute reference for the range in your formula.
=COUNTIFS($B$2:$B$18,B2,$A$2:$A$18,A2)

 

[sathishkm]

I have a huge set of data. In this case this formula take huge time. So if we need the vba macro it will be helpful.


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​

 

[Marc L]

Hi !

Excel inner features are often faster than any gas factory VBA code,
the reason why even in VBA formulas are used !

As yet written by Chihiro, via VBA
just use Application or Evaluate for any formula …
​

 

[NARAYANK991]

Hi ,

Two points :

1. If a formula in a worksheet takes a long time , I doubt that using the exact same formula in VBA and evaluating it using any VBA function will be less time consuming. I may be wrong.

2. Your requirement is tailor-made for a pivot table , which is a native Excel feature , and works on thousands of rows effortlessly.

See the attached file.

Narayan

 

[Marc L]

Obviously I agree with your both points !

Sometimes faster than a formula may be a loop on a memory variable array
but not easy to maintain for VBA beginners.

So when someone ask for a code without showing anything
- any code attempt neither any effort about an analyse or a logic to apply -
I prefer to stay with basics & essentials inner features …
​

 

[Hui]

Sum/Countif/s are remarkably efficient formulas even on huge ranges
I suspect there is more to the users model than just this table that is causing the slowdown.