COUNT and IF Array

[bob]

For every occurrence of a string in B9 in the current worksheet, I want to
count the number of cells that contain the letter W in Lineup Worksheet
F2:F83, where the string in Lineup Worksheet B2:B83 matches the string in B9.

I have tried using this formula but it counts all of the values in F2:F83; I
only want a count of those contaiing W.

=COUNT(IF($B9='Lineup Worksheet'!$B$2:$B$83,IF('Lineup
Worksheet'!$F$2:$F$83="W",0,0)))

Thanks,
Bob

 

[Pete_UK]

Try this:

=COUNTIF(Lineup!B$2:B$83,B9)

Hope this helps.

Pete

 

[Teethless mama]

=SUMPRODUCT(--($B9='Lineup Worksheet'!$B$2:$B$83),--('Lineup
Worksheet'!$F$2:$F$83="W"))

 

[daddylonglegs]

Hello bob,

personally I'd use SUMPRODUCT as Teethless mama suggests but the reason your
formula doesn't work is that your second if returns 0 whether TRUE or FALSE -
change to:

=COUNT(IF($B9='Lineup Worksheet'!$B$2:$B$83,IF('Lineup
Worksheet'!$F$2:$F$83="W",1)))

confirmed with CTRL+SHIFT+ENTER