I'm not sure how my example will show up, but here goes:
Column G has your formula in it.
Column H (in the cell under Team:) is where you type the team
for which you want to count results.
The formulas under the wins/draws/losses cells are array formulas. (Press
CTRL-SHFT-ENT
when typing them in rather than just pressing ENT)
You'll have to imagine the rows numbers:
win formula: =SUM(IF($E$4:$E$12=$H$2,IF($G$4:$G$12="W",1,0)))
draw formula: =SUM(IF($E$4:$E$12=$H$2,IF($G$4:$G$12="W",1,0)))
loss formula: =SUM(IF($E$4:$E$12=$H$2,IF($G$4:$G$12="W",1,0)))
If you have entered the formual correctly it will be enclosed in curly
braces {}.
Example:
-----------
columns
C D E F G
H
=================================================
Team:
BB
Team A Score Team B Score Result Wins Draws Losses
AA 5 BB 4 W 2 3 1
AA 10 BB 10 D
AA 15 BB 16 L
AA 5 CC 6 L
AA 10 BB 10 D
AA 15 BB 14 W
AA 5 BB 5 D
AA 10 CC 11 L
AA 15 CC 12 W
--
Les Torchia-Wells
"kreatiff" wrote:
Ok - I'm at that pulling out hair stage and I haven't got that much
left!
I'm using the formula below to determine whether a team has Won Drawn
or Lost a match based upon the result.
=IF($F4="","",(IF($F4<$C4,"W",(IF($F4$C4,"L",(IF( $F4=$C4,"D","")))))))
The result of the formula is either, W, D, or L.
I have created a small table that incorporates a list of the teams on
the left and three additional columns with a heading W, D, L
respectively. Using VLOOKUP add the following formula alongside each
team under the appropriate heading as below:
=VLOOKUP(H4,home,2)
H4 = Team name
Table array = home
Number of columns to move right = 2
This gives a result of #N/A.
What I want it to do is simply add the number of instances the club
name appears in my table array and count or add the number W, D, L it
sees.
Driving me nuts at the moment - so any pointers appreciated.
Many thanks in advance
J
--
kreatiff
------------------------------------------------------------------------
kreatiff's Profile: http://www.excelforum.com/member.php...o&userid=37732
View this thread: http://www.excelforum.com/showthread...hreadid=573223