# Re: Conditional Counting if Multiple Conditions Met

2317 4
cancel
Showing results for
Did you mean:
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?

5 - Automation Enthusiast

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…

5 - Automation Enthusiast

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:

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.)

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:

5 - Automation Enthusiast

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!