# Count or Frequency formula needed

#### Optimus_Primed036

##### New Member
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.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Teeroy

##### Well-known Member
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
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.

##### MrExcel MVP
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.

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

##### MrExcel MVP
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
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
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.

##### MrExcel MVP
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
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
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

Replies
15
Views
2K
Replies
1
Views
239
Replies
8
Views
485
Replies
13
Views
459
Replies
1
Views
802

1,170,931
Messages
5,872,774
Members
432,944
Latest member
mj02

### 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.

### Which adblocker are you using?

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

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