Pedro
asked on
Count Wins-Losses versus specific team
The attached Excel File contains team stats for specific Teams.
I'd like to be able to type a team in D1, G1, J1 and M1 and automatically calculate the number of wins losses for that team only.
For instance, If 'TOR' is typed in D1 then
B1 should contain all the times BAL (B4) won versus TOR (D1) While Cell
C1 should contain all the times BAL (B4) lost versus TOR (D1).
Ditto for the rest of the teams.
The Data in rows 3 and 4 calculate based on specific cell ranges.
Keep in mind that team data can be updated to include more data on the bottom most cells.
EE-Wins-LossesVsTeam.xlsx
I'd like to be able to type a team in D1, G1, J1 and M1 and automatically calculate the number of wins losses for that team only.
For instance, If 'TOR' is typed in D1 then
B1 should contain all the times BAL (B4) won versus TOR (D1) While Cell
C1 should contain all the times BAL (B4) lost versus TOR (D1).
Ditto for the rest of the teams.
The Data in rows 3 and 4 calculate based on specific cell ranges.
Keep in mind that team data can be updated to include more data on the bottom most cells.
EE-Wins-LossesVsTeam.xlsx
Please find attached.
EE-Wins-LossesVsTeam.xlsx
EE-Wins-LossesVsTeam.xlsx
ASKER
Phillip,
I am running into some issues with the formula you have in the worksheet.
For formula first reads,
{=SUM(IF(D2:D9999<>D1,0,IF
Then when I click in the cell the formula changes to ...
=SUM(IF(D2:D9999<>D1,0,IF(
It looks like the outerbrakets are removed, so I put them back in and the formula seems to work.
However, When I copy it to another workbook and change the respective cells, it does not work for me.
Instead of a number I see the actual formula in the cell.
{=SUM(IF(D7:D10005<>D6,0,I
It looks the same except for cell changes due to variations in data locations on a different workbook.
Please help!
I am running into some issues with the formula you have in the worksheet.
For formula first reads,
{=SUM(IF(D2:D9999<>D1,0,IF
Then when I click in the cell the formula changes to ...
=SUM(IF(D2:D9999<>D1,0,IF(
It looks like the outerbrakets are removed, so I put them back in and the formula seems to work.
However, When I copy it to another workbook and change the respective cells, it does not work for me.
Instead of a number I see the actual formula in the cell.
{=SUM(IF(D7:D10005<>D6,0,I
It looks the same except for cell changes due to variations in data locations on a different workbook.
Please help!
It's called an array formula.
You don't type the {} (curly brackets).
Instead, if you edit it, instead of pressing Enter to exit, you press Ctrl+Shift+Enter.
You don't type the {} (curly brackets).
Instead, if you edit it, instead of pressing Enter to exit, you press Ctrl+Shift+Enter.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
PS... My Houston Astros didn't do too bad against the teams listed here, at least on the AL side (11-11).
ASKER
Glen,
you got it!
B1: =SUMPRODUCT(--(B6:B227>C6:
C1: =SUMPRODUCT(--(B6:B227<C6:
etc, etc.
I altered your formula a bit in order to be able to copy across (i.e. removing the '$'). I like simple.
you got it!
B1: =SUMPRODUCT(--(B6:B227>C6:
C1: =SUMPRODUCT(--(B6:B227<C6:
etc, etc.
I altered your formula a bit in order to be able to copy across (i.e. removing the '$'). I like simple.
Glad I was able to help. I used absolute referencing for the first two example pairs just to keep things straight, but as long as it works, cool.
Regards,
-Glenn
Regards,
-Glenn
ASKER
Glen,
Thank you again.
P.S. I would also like the do the same using a table.
See below for link
https://www.experts-exchange.com/questions/28522133/extract-wins-losses-from-other-worksheet.html
Thank you again.
P.S. I would also like the do the same using a table.
See below for link
https://www.experts-exchange.com/questions/28522133/extract-wins-losses-from-other-worksheet.html
ASKER
Excellent Job!
Thanks Again!
Thanks Again!
ASKER
The above should read...
The Data in rows 3 and 5 calculate based on specific cell ranges.