Vlookup
Ok - I'm at that pulling out hair stage and I haven't got that muc left! I'm using the formula below to determine whether a team has Won Draw 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 o the left and three additional columns with a heading W, D, respectively. Using VLOOKUP add the following formula alongside eac 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. :mad: What I want it to do is simply add the number of instances the clu name appears in my table array and count or add the number W, D, L i sees. Driving me nuts at the moment - so any pointers appreciated. Many thanks in advance -- kreatif ----------------------------------------------------------------------- kreatiff's Profile: http://www.excelforum.com/member.php...fo&userid=3773 View this thread: http://www.excelforum.com/showthread.php?threadid=57322 |
Vlookup
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. :mad: 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 |
Vlookup
You could create a pivot table, based on your home table, to summarize
the data. There are instructions and links he http://www.contextures.com/xlPivot01.html When you create the pivot table, put team name in the row area, Win/Lose in the Column area, and another copy of team name in the data area, where it will show as Count of Team. 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. :mad: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Vlookup
Thanks to both of you to responding to my plea for help... Shouldn't I be using VLOOKUP ? and is it possible to SUM or COUNT usin VLOOKUP ? ----------- I'll have a close look at your suggestions :- -- kreatif ----------------------------------------------------------------------- kreatiff's Profile: http://www.excelforum.com/member.php...fo&userid=3773 View this thread: http://www.excelforum.com/showthread.php?threadid=57322 |
Vlookup
Les Its so easy to forget the basic stuff sometimes, thanks for you effort. I can see how this works... let you know how it goes -- kreatif ----------------------------------------------------------------------- kreatiff's Profile: http://www.excelforum.com/member.php...fo&userid=3773 View this thread: http://www.excelforum.com/showthread.php?threadid=57322 |
Vlookup
A VLOOKUP formula returns one value from a table, so it wouldn't sum or
count the values. Another option is to use SUMPRODUCT, as described he http://www.contextures.com/xlFunctio...tml#SumProduct substituting cell references for the typed values. kreatiff wrote: Thanks to both of you to responding to my plea for help... Shouldn't I be using VLOOKUP ? and is it possible to SUM or COUNT using VLOOKUP ? ----------- I'll have a close look at your suggestions :-) -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Vlookup
A further update: The design has moved on as I wasn't able to find a solution to use VLOOKUP to count or sum multiple cells: :) I've now created columns with the team name at the top of the spreadsheet going left to right. Underneath each team I have listed Week 1, Week 2 etc... going down the page, with alongside the result W, D, or L. At the bottom I have used the COUNT function to sum the total number of draws, wins or losses. I'm now having a problem - and really shouldn't be - with a straight forward IF statement. As LES offered earlier with his suggestion...:) using the following should do/say this: If range A12:A17 equals "TEAM NAME" then VLOOKUP L2 (which is the TEAM NAME) in table array A12:B17 select the value in the second column and return nothing if not found. =IF(A12:A17="TEAM NAME",VLOOKUP($L$2,A12:B17,2,0),"") :confused: However the result is always a blank cell, as it doesn't appear to recognise the TEAM NAME. -- kreatiff ------------------------------------------------------------------------ kreatiff's Profile: http://www.excelforum.com/member.php...o&userid=37732 View this thread: http://www.excelforum.com/showthread...hreadid=573223 |
Vlookup
Debra Thanks for your SUMPRODUCT option, I must try this and get back t you... Cheers J: -- kreatif ----------------------------------------------------------------------- kreatiff's Profile: http://www.excelforum.com/member.php...fo&userid=3773 View this thread: http://www.excelforum.com/showthread.php?threadid=57322 |
All times are GMT +1. The time now is 10:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com