Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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),"") 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? | Excel Programming | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |