Formula returns 0 with lots of data

A

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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
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.
 
Upvote 0
apc1968 said:
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
Click to expand...

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.
 
Upvote 0
apc1968 said:
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
Click to expand...
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.
 
Upvote 0
T. Valko said:
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.
Click to expand...

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

Thanks very much - really appreciated!!!

A
 
Upvote 0
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!
 
Upvote 0
apc1968 said:
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!
Click to expand...

Care to post a small sample along with the result you want to see?
 
Upvote 0
Aladin Akyurek said:
Care to post a small sample along with the result you want to see?
Click to expand...

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"]
<!--StartFragment--> <colgroup><col width="65" span="3" style="width:65pt"> </colgroup><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]
<!--EndFragment--></tbody>[/TABLE]


Hope that makes sense - thanks!!
 
Upvote 0
apc1968 said:
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!!
Click to expand...

Thanks for the sample and the example result...


[TABLE="width: 497"]
<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>[TR]
[TD="class: xl66, width: 104, bgcolor: transparent"]Name[/TD]
[TD="class: xl66, width: 131, bgcolor: transparent"]Policy Type[/TD]
[TD="class: xl66, width: 70, bgcolor: transparent"]Profit[/TD]
[TD="class: xl66, width: 165, bgcolor: transparent"]Occurrence Freq[/TD]
[TD="class: xl66, width: 66, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Jones[/TD]
[TD="class: xl67, bgcolor: transparent"]Home[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]60[/TD]
[TD="class: xl66, bgcolor: transparent"]Distinct Count[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Jones[/TD]
[TD="class: xl67, bgcolor: transparent"]Motor[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]55[/TD]
[TD="class: xl66, bgcolor: transparent"]Total[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]153[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]92[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]390[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Kane[/TD]
[TD="class: xl67, bgcolor: transparent"]Home[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]45[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Able[/TD]
[TD="class: xl67, bgcolor: transparent"]Home[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]33[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Kennedy[/TD]
[TD="class: xl67, bgcolor: transparent"]Motor[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]30[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Jones[/TD]
[TD="class: xl67, bgcolor: transparent"]Travel[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Webster[/TD]
[TD="class: xl67, bgcolor: transparent"]Home[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]45[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Williams[/TD]
[TD="class: xl67, bgcolor: transparent"]Motor[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]48[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Williams[/TD]
[TD="class: xl67, bgcolor: transparent"]Home[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]44[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Smith[/TD]
[TD="class: xl67, bgcolor: transparent"]Home[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]55[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Smith[/TD]
[TD="class: xl67, bgcolor: transparent"]Motor[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]90[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Smith[/TD]
[TD="class: xl67, bgcolor: transparent"]Travel[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]80[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

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)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Excel VBA - Check For Duplicates and Return the Count on a Large Data Set
Replies
4
Views
262
Kaper
Kaper
X
  • Question Question
VBA for extracting specific data from PDF Docs
Replies
3
Views
606
xcellrodeo
X
B
  • Question Question
Formula for count unique value in large data with one criteria?
Replies
5
Views
615
Peter_SSs
Peter_SSs
M
  • Question Question
Returning Values Based on Referent Cells (Avoiding Redundant Values)
Replies
5
Views
500
Fluff
Fluff
D
  • Solved
Cannot COUNT/SUM Displayed Table Values, Table Filtered Values Out of Order
Replies
3
Views
340
Alex Blakenburg
A

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top