Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Count Wins-Losses versus specific team

Posted on 2014-09-18
10
152 Views
Last Modified: 2014-09-19
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
0
Comment
Question by:Pedrov664
  • 5
  • 3
  • 2
10 Comments
 

Author Comment

by:Pedrov664
ID: 40330541
oooops!

The above should read...

 The Data in rows 3 and 5 calculate based on specific cell ranges.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40330564
Please find attached.
EE-Wins-LossesVsTeam.xlsx
0
 

Author Comment

by:Pedrov664
ID: 40330833
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(B2:B9999>C2:C9999,1,0)))}

Then when I click in the cell the formula changes to ...

=SUM(IF(D2:D9999<>D1,0,IF(B2:B9999>C2:C9999,1,0)))

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,IF(B7:B10005>C7:C10005,1,0)))}

It looks the same except for cell changes due to variations in data locations on a different workbook.

Please help!
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40330836
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.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40330888
You can use a modified version of your full season SUMPRODUCT formula in row 5 to show these results:

B1:  =SUMPRODUCT(--(B6:B227>C6:C227),--(D6:D227=$D$1))
C1:  =SUMPRODUCT(--(B6:B227<C6:C227),--(D6:D227=$D$1))
E1:  =SUMPRODUCT(--(E6:E227>F6:F227),--(G6:G227=$G$1))
F1:  =SUMPRODUCT(--(E6:E227<F6:F227),--(G6:G227=$G$1))

and similarly for the remaining cells in H, I, J, K

Sample file attached.

Regards,
-Glenn
EE-Wins-LossesVsTeam2.xlsx
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40330896
PS... My Houston Astros didn't do too bad against the teams listed here, at least on the AL side (11-11).
0
 

Author Comment

by:Pedrov664
ID: 40333063
Glen,

you got it!

B1:  =SUMPRODUCT(--(B6:B227>C6:C227),--(D6:D227=D1))
C1:  =SUMPRODUCT(--(B6:B227<C6:C227),--(D6:D227=D1))

etc, etc.
 
I altered your formula a bit in order to be able to copy across (i.e. removing the '$'). I like simple.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40333415
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
0
 

Author Comment

by:Pedrov664
ID: 40333765
Glen,

Thank you again.

P.S. I would also like the do the same using a table.

See below for link

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28522133.html
0
 

Author Closing Comment

by:Pedrov664
ID: 40333766
Excellent Job!

Thanks Again!
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question