Formula returns 0 with lots of data

[apc1968]

Hi - using Excel for Mac and have a large volume of data (40k records) - col A is customer name and col B is policy type.

To count how many customers have (say) 3 policies I was previously told to use:

To count how many customers have three policies,
=SUMPRODUCT(--(COUNTIF(A2:A40000,A2:A40000)=3))/3

This works with small amounts of data but when applied to my 40k records - it just returns a 0 (and calculate is still showing at the bottom) - forcing a recalc doesnt do anything either

Is it just my volume size that is the problem - is there another formula I can use - or do I need to ask for VBA help?

Thanks for any replies - I appreciate the help

A

 

[Aladin Akyurek]

Fabulous! Thank you

Have a great day

A

You are welcome. Thanks for providing feedback.

 

[T. Valko]

Hi - the previous formula would show that 2 customers have three policies - I want a formula as well which would show that those customers with 3 policies (2 of them) have total profits of £390 (the total of Jones and Smith)
[TABLE="width: 195"]
<TBODY>[TR]
[TD="class: xl63, width: 65"]Name
[/TD]
[TD="class: xl63, width: 65"]Policy
[/TD]
[TD="class: xl63, width: 65"]Profit
[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64"]Jones
[/TD]
[TD="class: xl64"]Home
[/TD]
[TD="class: xl65"]£60
[/TD]
[/TR]
[TR]
[TD="class: xl64"]Jones
[/TD]
[TD="class: xl64"]Motor
[/TD]
[TD="class: xl65"]£55
[/TD]
[/TR]
[TR]
[TD="class: xl64"]Kane
[/TD]
[TD="class: xl64"]Home
[/TD]
[TD="class: xl65"]£45
[/TD]
[/TR]
[TR]
[TD="class: xl64"]Able
[/TD]
[TD="class: xl64"]Home
[/TD]
[TD="class: xl65"]£33
[/TD]
[/TR]
[TR]
[TD="class: xl64"]Kennedy
[/TD]
[TD="class: xl64"]Motor
[/TD]
[TD="class: xl65"]£30
[/TD]
[/TR]
[TR]
[TD="class: xl64"]Jones
[/TD]
[TD="class: xl64"]Travel
[/TD]
[TD="class: xl65"]£50
[/TD]
[/TR]
[TR]
[TD="class: xl64"]Webster
[/TD]
[TD="class: xl64"]Home
[/TD]
[TD="class: xl65"]£45
[/TD]
[/TR]
[TR]
[TD="class: xl64"]Williams
[/TD]
[TD="class: xl64"]Motor
[/TD]
[TD="class: xl65"]£48
[/TD]
[/TR]
[TR]
[TD="class: xl64"]Williams
[/TD]
[TD="class: xl64"]Home
[/TD]
[TD="class: xl65"]£44
[/TD]
[/TR]
[TR]
[TD="class: xl64"]Smith
[/TD]
[TD="class: xl64"]Home
[/TD]
[TD="class: xl65"]£55
[/TD]
[/TR]
[TR]
[TD="class: xl64"]Smith
[/TD]
[TD="class: xl64"]Motor
[/TD]
[TD="class: xl65"]£90
[/TD]
[/TR]
[TR]
[TD="class: xl64"]Smith
[/TD]
[TD="class: xl64"]Travel
[/TD]
[TD="class: xl65"]£80
[/TD]
[/TR]
</TBODY>[/TABLE]


Hope that makes sense - thanks!!

This will work but will be slow to calculate on 40k rows of data:

=SUMPRODUCT(--(COUNTIF(A2:A13,A2:A13)=3),C2:C13)