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)

Name	Policy	Profit
Jones	Home	£60
Jones	Motor	£55
Kane	Home	£45
Able	Home	£33
Kennedy	Motor	£30
Jones	Travel	£50
Webster	Home	£45
Williams	Motor	£48
Williams	Home	£44
Smith	Home	£55
Smith	Motor	£90
Smith	Travel	£80

<TBODY>
</TBODY>

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)