Is there a faster formula for COUNTIF

[Dsewardj]

Hi,

I have 20,000 records pulled from two different extracts. The 20,000 records each have a material number with leading zeroes such as 0001342542, 000124312.

Right now I have a COUNTIF formula which counts the number of instances of material records found in the other extract.

I am using a pivot table to count the number of each record within their onw extract and this is blazingly fast.

Does anyone know of a faster or alternative method than using the COUNTIF function?

 

[JackDanIce]

MATCH function might be marginally faster but may not give as much information as COUNTIF does

 

[wigi]

You can only make the lookup area as tight as possible.

Alternatives: pivot tables, VBA-code.

 

[Dsewardj]

Right, I am currently using dynamic named ranges to expand and collapse to keep the ranges as small as possible.

I happen to know VBA pretty well but I am not certain there is anything in VBA which can perform these operations faster.

I was thinking I could have three pivot tables. The first two pivot tables would each have record counts for the two respective extracts. The third pivot table would combine both extracts together and count them again. I could then use either a VLOOKUP or MATCH+INDEX to find the records in the third pivot table.

It's a long way of going about it, but I believe it would be much faster than relying on the COUNTIF functions alone.

I was just hoping there was a simpler alternative.

 

[wigi]

I happen to know VBA pretty well but I am not certain there is anything in VBA which can perform these operations faster.

I meant to use a VBA-code to calculate the the result and store without formulas but rather hard-coded values.

 

[GlennUK]

Hi,

I have 20,000 records pulled from two different extracts. The 20,000 records each have a material number with leading zeroes such as 0001342542, 000124312.

Right now I have a COUNTIF formula which counts the number of instances of material records found in the other extract.

I am using a pivot table to count the number of each record within their onw extract and this is blazingly fast.

Does anyone know of a faster or alternative method than using the COUNTIF function?

Would using GETPIVOTDATA to try to fetch data from the pivottable of the other extract be a possibility? You'd get a #REF if the material number didn't exist in it, by the way.

 

[Dsewardj]

The MATCH function is much faster than the COUNTIF and I am actually using it once the COUNTIF portion of the spreadsheet is calculated, however it is not able to return the number of records, just their position.

Currently I am using VBA to remove the COUNTIFs after they have calculated.

.Range("matNumA").Value = .Range("matNumA").Value

The COUNTIFs take several minutes to calculate and I am using this spreadsheet very often.

I honestly think my best bet is a third pivot table. Once I have the results of the three pivot tables I can use either a VLOOKUP or MATCH+INDEX and then use simple math to determine the record count.

The only issue is the pivot tables will collapse and expand but I should be able to take care of this with VBA code.