Counting a range of stats if another range rule is met

**robmc** · 07-23-2012, 07:56 PM

I think this might be stretching a bit to far with what excel can do, but here goes...

I am creating a sheet that records the results of an individuals performances throughout a given season against multiple teams. So in column A there is a list of teams then in columns B2:Z2 are a list of various stats. Now a particular opponent may be played anywhere from 2-4 times a year. I add all the results in manually once they are known. What I want to do is to compile all results vs each opponent as a result.

So for example lets say Team 1 is played 4 times. In column A Team 1 appears 4 times, I want to collate the stats from each performance in to 1 result. A formula of
=IF(A2="Team 1",B2,"") will give me the result for that one match that appears in B2 (if the result in A2 is Team 1). But a team will appear in the schedule more than once and I want to collate performances against each team.

Of course I could do this manually by using the sum command and selecting each column that the team appears in, this is open to error though if I miss the team in question (A season may last anywhere from 20 to 80 games with a team appearing from 1-4 times, after a while this really can mess with your eyes and mistakes happen!). I was wondering if there was a forumla that I can use that will capture the data per team and collate it into one place for me automatically.

I have attached a sample of what I am talking about in the hope that it might explain things a little better (I hope it is attached anyway!)

Please help if you can...I have often underestimated what excel can do and have done things manually for years when in fact excel would've done the work for me!

**vandan_tanna** · 07-23-2012, 08:32 PM

=SUMIF($A$2:$A$12,$A17,B$2:B$12)

put above formula in B17, then drag it down and to the right

Note: $A$2:$A$12: Range to search
$A17: item to search for. Note no $ in front of 17. This is needed so that when you drag this down, item it searches for changes properly
B$2:B$12: range to sum. Again, note no $ in front of B. This ensures as you drag formula across different columns, it sums the right range

**robmc** · 07-23-2012, 09:03 PM

Thanks Vandan, it made sense once I got my head around what it was doing and it is exactly what I needed. I thought it was a sumif command but I couldn't quite get the context right and is not a command I have used before...I should really come here before I do things, this makes a lot of things much easier and also gives me some ideas on how to simplify some other stats that I use excel for that I have been doing manually...Excel is very versatile!

Thanks again for your help...with the amount of data that this collects you have saved me hours of work!