Count W in alternate cells

[Gazzal]

I have a pool score sheet and need to count the wins "W" in alternate rows

Each row has Name in col 1 then game results "W","L" or is blank in each col

For each date (top row) there is two cols, one doubles, one singles result

Eg;

A B C D E F G H I .... Z

1 2-Apr 9-Apr 16-Apr 23-Apr ....

2 John X W L L W W W L W .... 3

3 Bill O L W L L L W .... 2

I need to count the number of wins "W" for each SINGLES game in rows C,E,G,I etc

 

[Hui]

Gazzal


Firstly, Welcome to the Chandoo.org Forums


This should solve your problem

=SUMPRODUCT((B2:Z2 ="W")* (MOD(COLUMN(B2:Z2), 2) =1))


If you want to understand what is happening have a read of

http://chandoo.org/wp/2011/11/30/formula-forensics-no-004/


and for the Columns: B,D,F etc try:

=SUMPRODUCT((B2:Z2="W")*(MOD(COLUMN(B2:Z2),2)=0))

 

[Gazzal]

Thank You Hui, your a champ!!.