Count or Frequency formula needed

[Optimus_Primed036]

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.

 

[Teeroy]

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]

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]

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:

=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]

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]

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

=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]

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]

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	EVENT_ID	FULL_DESCRIPTION	COURSE	SCHEDULED_OFF	EVENT	ACTUAL_OFF	SELECTION	SETTLED_DATE	ODDS	LATEST_TAKEN	FIRST_TAKEN	IN_PLAY	NUMBER_BETS	VOLUME_MATCHED	SPORTS_ID	SELECTION_ID	WIN_FLAG
2	100738692	Echu (AUS) 19th Aug	Echu	19/08/2013 11:27	R1 2100m Mdn	19/08/2013 11:27	2. Baccarat Ben	19/08/2013 11:29	1.01	19/08/2013 11:29	19/08/2013 11:29	IP	5	762	7	7090254	1
3	100738692	Echu (AUS) 19th Aug	Echu	19/08/2013 11:27	R1 2100m Mdn	19/08/2013 11:27	2. Baccarat Ben	19/08/2013 11:29	1.06	19/08/2013 11:29	19/08/2013 11:29	IP	2	5.1	7	7090254	1
4	100738693	Echu (AUS) 19th Aug	Echu	19/08/2013 11:27	TO BE PLACED	6/09/2013 9:32	10. Hepburn Miss	19/08/2013 11:31	1.49	19/08/2013 11:25	19/08/2013 11:25	NI	2	0.1	7	7580977	0
5	100738693	Echu (AUS) 19th Aug	Echu	19/08/2013 11:27	TO BE PLACED	6/09/2013 9:32	10. Hepburn Miss	19/08/2013 11:31	1.51	19/08/2013 11:25	19/08/2013 11:25	NI	2	0.1	7	7580977	0
6	100738694	Echu (AUS) 19th Aug	Echu	19/08/2013 12:02	R2 1000m 3yo	19/08/2013 12:03	5. Newspaper	19/08/2013 12:06	7	19/08/2013 12:04	19/08/2013 12:04	IP	3	14	7	7249074	0
7	100738694	Echu (AUS) 19th Aug	Echu	19/08/2013 12:02	R2 1000m 3yo	19/08/2013 12:03	5. Newspaper	19/08/2013 12:06	1000	19/08/2013 12:04	19/08/2013 12:04	IP	2	0	7	7249074	0

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

Since you have PI or non IP in L...

Control+shift+enter:

=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]

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

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