ARRAY - Counting Games Played

[bob]

I am trying to calculate how many games a player named "Arenas" has played
based on data in two worksheets, "Source" and "Chart1." In Source, the
entries in col A are as follows:

COL A
Arenas
Butler
Haywood
Thomas
Stevenson
Brown
Smith

In Chart1 the entries in cols A and E are as follows.

COL A COL E
4/23/08 Arenas
4/24/08 Butler
4/24/08 Arenas
4/23/08 Haywood
4/23/08 Arenas
4/26/08 Thomas
4/26/08 Thomas
4/26/08 Arenas
4/26/08 Arenas

Each unique player and date combination counts as 1 game played. In this
example, it is evident from the data in Chart1 that Arenas has played in 3
games (4/23, 4/24, 4/26) even though his name is listed 5 times, two of which
are for games on the same dates (4/23 and 4/26).

From the Source sheet, I want a formula that will calculate this example,
keeping in mind that the rows of data are actually 1 to 20000

Can anyone help?

Thanks,
Bob

 

[ShaneDevenshire]

Hi,

Here I have a example in which all the data is on the same sheet, just add a
sheet reference.

=SUM(--(FREQUENCY(IF((A1=BS13:BS21)*AS13:AS21>0,(A1=BS13:BS21)*AS13:AS21,""),(A1=BS13:BS21)*AS13:AS21)>0))

This formula is array entered. And the A13:A21 and B13:B21 ranges are the
ranges you have on the second sheet near the top? In other words place the
two column lookup table in A13:B21 of the same sheet as the values to be
looked up, to test the formula.

 

[Ashish Mathur]

Hi,

IN column F of sheet named "chart1", enter the following formula E2&A2 and
copy down. In column G, enter the following formula =COUNTIF($F$2:F2,F2)
and coy down. Now enter the following formula in
SUMPRODUCT(($E$2:$E$10=A14)*($G$2:$G$10=1)). Cell A14 has "Arenas"

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com