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

 

[Marcelo Branco]

See if this is faster

Array formula

=SUM(IF(FREQUENCY(IF(A2:A40000<>"",MATCH(A2:A40000,A2:A40000,0)),ROW(A2:A40000)-ROW(A2)+1)=3,1))

confirm with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

M.

 

[Aladin Akyurek]

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

Try also...

=SUM(IF(FREQUENCY(IF(A2:A40000<>"",MATCH(A2:A40000,A2:A40000,0)),
  ROW(A2:A40000)-ROW(A2)+1)=3,1))

which needs to be confirmed with control+shift+enter, not just enter.

 

[T. Valko]

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

Try this array formula**

=SUM(IF(FREQUENCY(MATCH(A2:A40000,A2:A40000,0),ROW(A2:A40000)-ROW(A2)+1)=3,1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[apc1968]

Try this array formula**

=SUM(IF(FREQUENCY(MATCH(A2:A40000,A2:A40000,0),ROW(A2:A40000)-ROW(A2)+1)=3,1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Fabulous - all three of the above formulas work - my job is now to figure out how and why....!

Thanks very much - really appreciated!!!

A

 

[apc1968]

Can I ask another question? In column C I have the earnings from each policy - the above tells me that 1,800 customers have 3 policies. Can I figure out a formula to tell me how much I earn from those 1,800?

Thank you!

 

[Aladin Akyurek]

Can I ask another question? In column C I have the earnings from each policy - the above tells me that 1,800 customers have 3 policies. Can I figure out a formula to tell me how much I earn from those 1,800?

Thank you!

Care to post a small sample along with the result you want to see?

 

[apc1968]

Care to post a small sample along with the result you want to see?

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

<!--StartFragment--> <colgroup><col width="65" span="3" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

Hope that makes sense - thanks!!

 

[Aladin Akyurek]

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!!

Thanks for the sample and the example result...

Name	Policy Type	Profit	Occurrence Freq	1	2	3
Jones	Home	60	Distinct Count	4	1	2
Jones	Motor	55	Total	153	92	390
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

<COLGROUP><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3697" width=104><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4664" width=131><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2474" width=70><COL style="WIDTH: 124pt; mso-width-source: userset; mso-width-alt: 5859" width=165><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2332" width=66><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

E2, control+shift+enter, not just enter, and copy across:

=SUM(IF(FREQUENCY(IF($A$2:$A$13<>"",MATCH($A$2:$A$13,$A$2:$A$13,0)),
  ROW($A$2:$A$13)-ROW($A$2)+1)=E$1,1))

E3, control+shift+enter, not just enter, and copy across:

=SUM(IF($A$2:$A$13<>"",IF(COUNTIF($A$2:$A$13,$A$2:$A$13)=E$1,$C$2:$C$13)))

 

[apc1968]

Fabulous! Thank you

Have a great day

A