Help

Re: Conditional Counting if Multiple Conditions Met

2320 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Nabeel_Hyatt
5 - Automation Enthusiast
5 - Automation Enthusiast

I’ve tried a bunch of formulas and various table structures and just can’t seem to find a way to get what I need.

So let’s say I’m running a kids soccer league, or an e-sport, or really anything close. In other words I have games:

  1. Celtics Win - Pacers Lose
  2. Pacers Win - Celtics Lose
  3. Cavaliers Lose - Celtics Win
    …etc

Across a season I want to be able to say, naturally, how many times the Celtics W/L - that’s easy doing Rollups and such.

The problem I’m having is conditional relations, what I would have used COUNTIF for in Excel.

A perfect example is, what is the W/L record of the Celtics vs the Pacers? I can’t seem to figure that out.

16 Replies 16

Except you’re looking for a solution that doesn’t require you to name ‘team a’ and ‘team b’ explicitly, right?

Just for explorations sake, how would you do it explicitly?

Generally of course I’m not looking to generate the result once, but in a formulaic way so you can work your way through all the matchups. If there’s a manual way to do it then, theoretically you could kludge a solution manually.

I’m trying to remember why I asked that and then stopped; I suspect it had something to do with someone knocking on the door and asking if they could look at the property. (Working at home becomes a lot less efficient once your landlord decides to sell.)

There was one day earlier this week where I ran into a blind exit on three bases in a row; I’d read a post, think “I know a way to do that,” start slapping together a demo base… and slam to a halt. I wasn’t sure if I was being limited by Airtable intractability or by misfiring neurotransmitters, so I simply put everything aside until I could look at things afresh — which I guess I should do now.

As you mentioned, there’s a brute-force way of doing this, with a stupidly long IF() statement; I think there’s also a moderately manually intensive way that requires an explicit formula per team, but at least doesn’t require one per team per team. At the time I sent my last reply, I think I had a tentative way of doing it that didn’t necessarily require an explicit formula per team, even — but I hadn’t worked out all the bugs.

I’l look back over this tonight and see if what I’d thought still holds up…

Hah. And yes, I definitely know how to do this in excel with a very long IF statement. In case it gets your brain firing here’s my statement in excel right now (though it does this and a whole lot more).

=IF(’#Matchups (St)’!G9="","",IF(AND(‘Total Matches (St)’!B6>0.01,SUM(‘Total Matches (St)’!B6)>‘Meta (St)’!$B$5),(‘Wins (St)’!B6/(‘Wins (St)’!B6+‘Losses (St)’!B6)),’#Matchups (St)’!G9))

but the key here is it grabs the name of Team 1 from the row, and team 2 from the column, then does a lookup against all the matches in a match table to count wins, losses, and divide by total matches. Obviously in a database like Airtable I don’t expect it to be the exact same. But just in case it jogs a little something! :slightly_smiling_face:

Screen Shot 2018-05-10 at 8.47.57 PM.png

OK, here’s most of a completed base that kind of does what you want. As published, it supports up to 10 teams, but it can easily be expanded to a larger number. At the moment there are 8 defined; I recommend you add a 9th to see how the base works.

First, though, I should mention there are some shortcuts taken here and there that could be smoothed out. For instance, if you look at the single selects for {Winning Team} and {Losing Team} in the [Games] table, you’ll notice I’ve appended an index number to each. Elsewhere in the base, such things function more as one would expect, with the base associating a certain index value with a specific team name, but early in its development I didn’t want to get wrapped around the axle of establishing and referencing these index numbers, so I simply made them hard-coded. I was about to go back and fix them when it occurred to me I was spending a lot of time on a freebie proof of concept base, so if they bother, have at them. You’ll find other such liberties taken throughout the base.

I’m also not sure why I have both single selects and linked records used to define winners and losers in [Games]. I seem to think I added the selects simply to feed the pivot table, so you could easily eliminate them if you wish. Alternatively, you could use them the way I have been during development: First, I set the winning and losing teams using the drop-down menus. I then copy the newly entered data (select first-entered {Winning Team} and last-entered {Losing Team} — that is, the upper-left and lower-right cells of the block — and press Ctrl-C) and paste it into the first empty cell in {Link to Winning Team}. This populates the linked records fields corresponding to the recently entered single-selects and creates new records as needed.

To add a new team to the league, then, the first step would be to create new options in the configuration of {Winning Team} and {Losing Team}.The options should be in the format 'Team Name - ##' where '##' is the index number assigned to the team. The same index number should be used for both winning and losing entries. (You may notice I fat-fingered several entries, so that some read 'Team Name - ##' and others 'Team Name -##'. As it turns out, either will work; what wouldn’t work would be 'Team Name- ##'. I didn’t go back and correct the erroneous ones because doing so would have caused me to generate new records for the corrected entries — another downside to my expediency)

You can then either enter a placeholder record for the team in the [Winning Team] and [Losing Team] tables or wait for the records to be created as a matter of course.

Caveat: The base uses entries in [Winning Team] to create much of the data it requires for self-management. Should a newly entered team not prove very successful on the field, you’ll want to create a [Winning Team] record manually. (The bulk of the fields in [Winning Team] simply feed other aspects of the base; in practice, probably everything to the right of {Winning Matches} would be hidden.)

{Winning Matches} is just as it sounds: The table contains a record for each team tracked by the base, as indicated by the primary field; {Winning Matches} lists every team over which the primary team has triumphed, along with a count of how many times the primary team has beaten them. It exists mainly as an example to show the data is available from the base; you’ll probably want to access and expose the data in other ways. (I’ll demonstrate one such other way a little later.)

When you add a team, you’ll need to make a couple minor changes in the [Winning Team] and [Losing Team] tables: Namely, you’ll need to establish a link to the [Calc] table from the new record. You can do this manually, by clicking on the plus sign in the {Calc} field and
selecting the single record in [Calc]; you may copy-and-paste a period ('.') character into the {Calc} field; or you can click-and-drag the fill handle from the {Calc} field in the previous row to the current row, thus duplicating the link to the record entitled '.'. You’ll need to establish such a link in both the winning and losing tables.

As mentioned earlier, the remainder of the fields in [Winning team] support internal processes. It is here that, based upon {Autonumber}, the number of currently active teams is determined; it is also here that a {KeyValue} pair is extracted to associate an index number with a team name. Other fields reflect a summarizing process taking place in [Calc]; for instance, {TeamNames} returns a list of key:value pairs concatenated within [Calc] but parsed here to extract names for use in {Winning Matches}.Finally, {GamesOne} packs a triple-digit count of games won by the indicated team against each other team in the roster into a single string.

You’ll find [Losing team] to be very similar, although it boasts fewer internal data fields. It offers a {Losing Matches} field indicating how many games had been lost to each opposing team as well as a packed {Games Lost} field.

The [Calc] and [Grid Background] tables exist primarily to carry out calculations. One of [Calc]'s primary duties is to build a matrix of team:team matchups based on the packed {Games Won} and {Games Lost} fields. That matrix is then accessed by the [Grid Background] table, which uses it to construct cross-record, per-team display fields that are later rolled up into a single field expressing data about multiple records.

I use a variation on a hack I devised for the Black Mirror base in Airtable Universe to provide a display similar to your Excel example. (It will look better as you add games to the database: It’s designed to provide 10 levels of gradation between 0% and 100%, transitioning from red at 0 to yellow at 50 to green at 100.)

WinLoss01

And, obviously, it’s incomplete: The X axis needs to be labeled (the columns are team names with index values 1 through 8, left to right) and, more importantly, the formula for the {Winners} field needs to be tweaked so the percent values properly align with the colored fields. It’s simply a process of adding and subtracting HTML space characters of varying widths to the assorted overlapping equations that create the final image… (I’ve done it before; now it’s your turn.)

In any case, there’s your expandable base that will track the win:loss records of a number of teams; that doesn’t need to have teams explicitly named in its code; and that can be easily converted to a number of other uses.

OK, this bothered me, so I fixed it.

I’ve updated the demo base so the won/lost matrix displays correctly. (The two extra columns to the right are where the 9th and 10th teams would go; feel free to delete the heading placeholders if you wish.)

Dammit.

I just thought of a significant improvement. Well, if I implement it, I’ll add yet another reply.

I also Greeked in some additional games, to fill out the matrix somewhat:

WinLoss02.png

This is amazing. I mean, it’s obviously a kludge and I wish Airtable supported it more natively, but you’ve definitely solved the problem!