SumProduct help please

[gazzal]

I was given this formula by you guys to read the number of "W" (Wins) in alternate cols in a row.
SUMPRODUCT((B25:AA25 ="W")* (MOD(COLUMN(B25:AA25), 2) =1))
But we've had a system change and now need to read the number of W's when there could be 'WW', 'LW' or 'WL' in a col.
Can someone assist us to get our program working again please.

[MarvinP]

Perhaps

Please Login or Register  to view this content.

[FDibbins]

Or maybe just...
SUMPRODUCT((B25:AA25 ="*W*")* (MOD(COLUMN(B25:AA25), 2) =1))
(untested)

[Ochimus]

Try

SUMPRODUCT((B25:AA25 ="W")*(B25:AA25 ="LW")*(B25:AA25 ="WL") *(B25:AA25 ="WW"))*(MOD(COLUMN(B25:AA25), 2) =1))

Ochimus

[humdingaling]

this maybe?

Please Login or Register  to view this content.

[humdingaling]

Ford...cant wildcard like that
you would need to do something like this instead

Please Login or Register  to view this content.

[FDibbins]

yeah I kinda figured that, too - thanks

[humdingaling]

Gaz
not sure if your data still has single "W" or not

if not then both solutions in 5 and 6 work
if yes, and you dont want to count it then use #5 if you do want to count it then use #6

[gazzal]

Thanks Humdingaling
No I don't need the "W".
Your formula is not counting the number of "W"s, it's counting "WW" as one when it should be 2.

[gazzal]

This gets it going but is it a good formula?
SUMPRODUCT((B5:AA5 ="WW")+(B5:AA5 ="WW")+(B5:AA5 ="WL")+(B5:AA5 ="LW")*(MOD(COLUMN(B5:AA5), 2) =1))

[FDibbins]

do you have a sample workbook to share?

[gazzal]

Workbook attached. It's just a small sample I've thrown together.

[humdingaling]

Please Login or Register  to view this content.

your doing WW twice here?

[gazzal]

Yes and it works in all tests I've done.
That's why I asked if it's a good formula. I think just counting the "W"s in a row may be better? but the multiple WW throws my ideas out.

[humdingaling]

not sure what you are counting

Please Login or Register  to view this content.

this formula searchs for anything with "W" which seems to be what you want? but not really based on your sample file?

[gazzal]

Last season we played one game of pairs and one game of singles (W|W) each week, this season one game of pairs and two games of singles (W|WW).
So we need to count the number of wins and losses in alternate cols and I'm having problems counting the WW in a formula.
As I've stated the formula SUMPRODUCT((B5:AA5 ="WW")+(B5:AA5 ="WW")+(B5:AA5 ="WL")+(B5:AA5 ="LW")*(MOD(COLUMN(B5:AA5), 2) =1)) works fine but is it a good formula with the two (B5:AA5 ="WW") in it. Will this cause unforseen problems?.
Your last formula =SUMPRODUCT(--ISNUMBER(SEARCH("W",B25:AA25)*(MOD(COLUMN(B25:AA25),2)=1))) does'nt count the 2nd W in "WW".

[humdingaling]

arrr ok you want to count the total amount of W's
that would be should be ok

Please Login or Register  to view this content.

this would be much simpler, in case you get "WWW" to contend with next year

[gazzal]

[SOLVED] Thanks muchly Humdingaling

[micope21]

Hi

Please Login or Register  to view this content.

copy down

For L? Just change WW to LL.

For Total just add W then put in I like this

Please Login or Register  to view this content.