multiple criteria in COUNTIF

[Johnathon]

Hi,

basicalli i have three columns of names. i have three
cells at the top of the sheet which the user can type
into. the three columns look like this, i have used
letters for now:

Leader Players
A E C
B B D
C D S
D E W
A F A
C D F
D E S
A S D
B A W
B C A
A E C


i need a formula that counts the amount of times three
people have been together. like if the user types in the
names "a" "e" "c" into the three cells at the top of the
sheet, the formula would show 2 becuase this combination
of players have played twice. i tried the Countif
function but i need it to let me type tree criteria.

are there any ideas that would help me to count. i am
using Windows XP with Excel EP as well. and i have a very
large list to count through and so that needs to be taken
into consideration as well.

thanks agains,
John

 

[Don Guillett]

=sumproduct((a2:a200=a1)*(b2:b200=b1)*(c2:c200=c1))