Formula returns 0 with lots of data

apc1968

New Member
Joined
Nov 4, 2012
Messages
14
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,178
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...
Rich (BB code):
=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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

New Member
Joined
Nov 4, 2012
Messages
14
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

New Member
Joined
Nov 4, 2012
Messages
14
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,178
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

New Member
Joined
Nov 4, 2012
Messages
14
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)
NamePolicyProfit
JonesHome£60
JonesMotor£55
KaneHome£45
AbleHome£33
KennedyMotor£30
JonesTravel£50
WebsterHome£45
WilliamsMotor£48
WilliamsHome£44
SmithHome£55
SmithMotor£90
SmithTravel£80

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


Hope that makes sense - thanks!!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,178
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...


NamePolicy TypeProfitOccurrence Freq123
JonesHome60Distinct Count412
JonesMotor55Total15392390
KaneHome45
AbleHome33
KennedyMotor30
JonesTravel50
WebsterHome45
WilliamsMotor48
WilliamsHome44
SmithHome55
SmithMotor90
SmithTravel80

<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:
Rich (BB code):
=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:
Rich (BB code):
=SUM(IF($A$2:$A$13<>"",IF(COUNTIF($A$2:$A$13,$A$2:$A$13)=E$1,$C$2:$C$13)))
 

Watch MrExcel Video

Forum statistics

Threads
1,099,529
Messages
5,469,206
Members
406,642
Latest member
Robson Moreira

This Week's Hot Topics

Top