Count or Frequency formula needed

Optimus_Primed036

New Member
Joined
Sep 20, 2012
Messages
9
Hi all,

Wondering if anyone can help. I want to count how many times an event occurs. Each event has an individual ID but it can appear many times.
Once established i only want to include those with "IP" in the same row.

For example:

A2=100
A3=100
A4=200

B2=IP
B3=IP
B4=PE

The answer to this should be "1" as there is only one ID (100) with "IP" in same row even though it appears twice.

Hope that made sense.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
Try:

=IFERROR(SUMPRODUCT(--(A2:A4=100),--(B2:B4="IP"))/SUMPRODUCT(--(A2:A4=100),--(B2:B4="IP")),0)

The SUMPRODUCT will count the number of occurrences then divide by that number so you get the one (1) you are after. If there are no occurrences the IFERROR gives you 0 rather than a #DIV0! error.
 

Optimus_Primed036

New Member
Joined
Sep 20, 2012
Messages
9
Thanks Teeroy that works for this example - but the number "100" was just one example of many thousands of varied numbers. Some have "IP" in column B which i want to include. Some don't.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Hi all,

Wondering if anyone can help. I want to count how many times an event occurs. Each event has an individual ID but it can appear many times.
Once established i only want to include those with "IP" in the same row.

For example:

A2=100
A3=100
A4=200

B2=IP
B3=IP
B4=PE

The answer to this should be "1" as there is only one ID (100) with "IP" in same row even though it appears twice.

Hope that made sense.

Looks like...

Control+shift+enter, not just enter:
Rich (BB code):
=SUM(IF(FREQUENCY(IF(A2:A100<>"",IF(B2:B100="IP",
  MATCH(A2:A100,A2:A100,0))),ROW(A2:A100)-ROW(A2)+1),1))
 

Optimus_Primed036

New Member
Joined
Sep 20, 2012
Messages
9

ADVERTISEMENT

Thanks Aladin, it works in my mini example but when i past it in to my 'real' example with thousands of numbers the result doesn't go past "1".

A formula i got from another chap was: =SUM(IF(FREQUENCY(IF(COUNTIFS(A2:A147998,A2:A147998,L2:L147998,"IP*"),A2:A147998),A2:A147998),1)) which may give you a better idea of what i'm doing.

This one kinda worked but i'm not entirely confident in it....
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Thanks Aladin, it works in my mini example but when i past it in to my 'real' example with thousands of numbers the result doesn't go past "1".

A formula i got from another chap was: =SUM(IF(FREQUENCY(IF(COUNTIFS(A2:A147998,A2:A147998,L2:L147998,"IP*"),A2:A147998),A2:A147998),1)) which may give you a better idea of what i'm doing.

This one kinda worked but i'm not entirely confident in it....

You have apparently provided the other chap the right ranges...

You seem to want a unique count of items which correspond to the IP records...

Control+shift+enter...
Rich (BB code):
=SUM(IF(FREQUENCY(IF(A2:A147998<>"",IF(LEFT(L2:L147998,2)="IP",
   MATCH(A2:A147998,A2:A147998,0))),ROW(A2:A147998)-ROW(A2)+1),1))
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368

ADVERTISEMENT

Hi, OP

Suggest you post some more sample data so the exact requirement is clear.

BTW, perhaps a pivot table is suitable?

regards
 

Optimus_Primed036

New Member
Joined
Sep 20, 2012
Messages
9
Excel 2010
ABCDEFGHIJKLMNOPQ
1EVENT_IDFULL_DESCRIPTIONCOURSESCHEDULED_OFFEVENTACTUAL_OFFSELECTIONSETTLED_DATEODDSLATEST_TAKENFIRST_TAKENIN_PLAYNUMBER_BETSVOLUME_MATCHEDSPORTS_IDSELECTION_IDWIN_FLAG
2100738692Echu (AUS) 19th AugEchu19/08/2013 11:27R1 2100m Mdn19/08/2013 11:272. Baccarat Ben19/08/2013 11:291.0119/08/2013 11:2919/08/2013 11:29IP5762770902541
3100738692Echu (AUS) 19th AugEchu19/08/2013 11:27R1 2100m Mdn19/08/2013 11:272. Baccarat Ben19/08/2013 11:291.0619/08/2013 11:2919/08/2013 11:29IP25.1770902541
4100738693Echu (AUS) 19th AugEchu19/08/2013 11:27TO BE PLACED6/09/2013 9:3210. Hepburn Miss19/08/2013 11:311.4919/08/2013 11:2519/08/2013 11:25NI20.1775809770
5100738693Echu (AUS) 19th AugEchu19/08/2013 11:27TO BE PLACED6/09/2013 9:3210. Hepburn Miss19/08/2013 11:311.5119/08/2013 11:2519/08/2013 11:25NI20.1775809770
6100738694Echu (AUS) 19th AugEchu19/08/2013 12:02R2 1000m 3yo19/08/2013 12:035. Newspaper19/08/2013 12:06719/08/2013 12:0419/08/2013 12:04IP314772490740
7100738694Echu (AUS) 19th AugEchu19/08/2013 12:02R2 1000m 3yo19/08/2013 12:035. Newspaper19/08/2013 12:06100019/08/2013 12:0419/08/2013 12:04IP20772490740

<tbody>
</tbody>
bf_au_hr-all-2013-08-19-2013-08
Sorry to stuff you around guys this is my first go at pasting a proper sample. I am only interested in A and L. The answer for this sample should be "2" as there are only 2 event ID's that have IP in the "L" column. I don't want to count duplicates.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Since you have PI or non IP in L...

Control+shift+enter:
Rich (BB code):
=SUM(IF(FREQUENCY(IF(A2:A147998<>"",IF(L2:L147998="IP",
   MATCH(A2:A147998,A2:A147998,0))),ROW(A2:A147998)-ROW(A2)+1),1))

Control+shift+enter means: Press down the control and the shift keys at the same time, while you hit the enter key.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Hi

As you are likely to have a large amount of data, a database-type approach is recommended (for best performance). Such as query table. The SQL could be

Code:
SELECT COUNT(*) AS YourCount
FROM (SELECT DISTINCT EVENT_ID
FROM [your sheet name$]
WHERE IN_PLAY = 'IP')
This can be set up via menu ALT-D-D-N. Please post again if you want further info on this.

regards
 

Forum statistics

Threads
1,137,296
Messages
5,680,672
Members
419,924
Latest member
Dhamodharan992

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
Top