Another Excel formula question

[SourChipmunk]

I'm sure this has been answered, but I am not having any success doing a search for it. So here goes...

I have a spreadsheet with a game schedule. The opposing team name is in column C, and the win/loss status of that game is in column F. There are 18 teams we play throughout the season, and about 100 games.

I would like to determine in one cell exactly how many games we have won/lost against any particular team throughout the season.

I know it isn't that much data, and I can just filter by team name and count it manually, but this is something I would like to learn for future reference.

Let me know if any more data is needed for this function. I think I can do it with a pivot table, but I just can't figure out how to use that feature.

 

[Antar Bolaeisk]

Could you set up a hidden column with =if(Fx="win",1,0) and just sum that column?

 

[wonder_lander]

I would say a pivot table but it's going to be easier if you can share the spreadsheet so we can see what you have and what you want to achieve.

 

[SourChipmunk]

Antar - did that but the dilemma is that it doesn't sum by team. I would need a way to mate the data in column C with the W/L status in column F.

But I got it! The old post-and-solve trick. A pivot table worked perfectly - my problem was with the rule that there can't be any blank cells in the table you're referencing.

I would say a pivot table but it's going to be easier if you can share the spreadsheet so we can see what you have and what you want to achieve.

I must do that next time.

 

[Mark M]

I dont follow your explanation 100% but Im sure a sumif could do it with a hidden column? Post up a sample spreadsheet so we can take a look.

 

[SourChipmunk]

Sure guys. Here is a sample spreadsheet using the Detroit Tigers games for the season. I don't know how to host/link a spreadsheet, but here's an image.

Again, I ended up using a pivot table @H14 and it worked beautifully once I finally learned how to do it.