For a pure formula approach, if you sort the data on column G and then use:

=IF(G2=G1,J1,COUNTIF(G:G,G2))

copied down, that should be considerably faster. It took about 7-8 seconds on my machine (running in a VM on my MacBook). Using a GETPIVOTDATA formula produced similar timings (not taking advantage of sorting). Adjusting the GETPIVOTDATA to take advantage of sorted column G made the calculation pretty much instantaneous.

So many great ideas, pleased I found this forum and asked the question.
Thanks to all of you.

Okay, so I've implemented both methods.

Velouria's formula =IF(G2=G1,J1,COUNTIF(G:G,G2)) took about 20 seconds to calculate (it actually took longer to copy the formula down the whole column before calculating).

But Riny's formula using VLOOKUP on a pivot-table took less than 10 seconds - I've attached a screenshot of the outcome where I placed the pivot-table on the same worksheet as the source-data.

After running both methods, I deduped the data on OFFENCE, so that I could sum the total to confirm = 809,731 which of course it did in both cases.

Really impressed with your thinking guys, thanks again for lending your expertise to this.
I'll definitely be back on here the next time I'm struggling,

regards, Chris.

My system

OS W11-64 bit 

Intel Core i7-6700 3.4GHz 16 GB Ram

I ran additional COUNTIFS calculations for each of the 361 offence types, to calculate the sub-totals for 'Plea Guilty' (column L in the Example 1 screenshot attached) and another calculation for 'Plea Not Guilty' (column M) - they took even longer than the previous calculations.

I've extended the pivot-table to include the sub-totals for 'Guilty' and 'Not Guilty' for each of the 361 offence-types as shown in Example 2 screenshot.
As indicated with the red-circles, the total number of rows for each offence-type is copied into the main table using a VLOOKUP on the pivot-table data as suggested by Riny above.
Now I want to use a similar VLOOKUP to populate columns K and L each indicated by colours green and blue.
How do I do this given the format of the pivot-table i.e. Guilty and Not Guilty sub-totals in the lines under each offence category?

Is there some way of formatting the pivot-table so that there's still only 1 row for each of the 361 offence-types, with 'Count of Offence', 'Count for Guilty', and 'Count for Not Guilty' in consecutive columns?
So that I can use the same formula to look-up each of the 3 numbers by simply changing the column-reference

e.g. =VLOOKUP(G127,$N$4:$O$364,2,FALSE) pulls the 'Count of Offence' from column 2
  But =VLOOKUP(G127,$N$4:$O$364,3,FALSE) would pull the 'Count for Guilty' from column 3 etc.

Chris

A GETPIVOTDATA formula would be more efficient and doesn't care about the pivot table layout (as long as the specified value cell appears in the pivot table somewhere).

Thanks Velouria. I'll take a look at the GETPIVOTDATA formula, learn how to use that function.

But just for possible future benefit, is there a solution to my previous question?

Is there some way of formatting the pivot-table so that there's still only 1 row for each of the 361 offence-types, with 'Count of Offence', 'Count for Guilty', and 'Count for Not Guilty' in consecutive columns?

Just drag the 'Plea' column to the Column area. I attached a very small scale example, showing what you have and what you want.

Thanks Riny. I should have had a play with the pivot-table options myself re. columns.
As always, looks simple once you know how.

I tried the GETPIVOTDATA approach too, amazed how powerful that option is.

So, all in all, you've all helped me learn three different solutions to my slow COUNTIF problem.
Much appreciated, thanks.