Conditional Counting if Multiple Conditions Met

Anyone else who has done work on recreating COUNTIFs? in Airtable? I’ve tried reading through the documentation but not sure if it is possible.

What I’m trying to do, if it’s not clear, is count in a Table how many times a specific “pairing” of values exists in a row. Aka “COUNTIF Celtics W and Pacers L” with the goal that at the end you would have Celtics are 4-3 vs the Pacers.

What are your Fields in the Games table?

Sure.

Match time/date, Winning Team, Losing Team, some other match parameters.

• Then imagine 100s of those for various matches.
• Then another table that has a list of all the potential teams to pull from.
• So it’s easy to generate “How many times has winning team won” with a rollup. But we are talking about something like “How many times has winning team won/lost against a giving losing team” - and visa versa.

Probably not the solution you’re looking for, but one possibility would be to use the Pivot Table Block:

Go mighty, mighty thrips! (queue charge song played on the kazoo) :trumpet:

http://mrec.ifas.ufl.edu/lso/THRIPS/CHILLIWEB2/ChilliThrips5_101.jpg

I was originally going to name the team after a wasp that parasitizes thrips; it’s also the third-smallest known insect, as this illustration of it next to a Paramecium and an amoeba at the same scale shows.

But then I thought, when going up against the mighty Mantis Shrimp — whose namesake has eyes with twelve to sixteen color receptors (humans have three), can see six different types of polarized light, and throws so fast a punch, the collapse of bubbles left in its wake can cause the surrounding seawater to reach temperatures as high as 8,500° F and emit brief flashes of light — what possible chance could the fearless Fairy Wasps have?

Pivot Table blocks are getting closer to what I need, for sure. They can’t really display the necessary information though.

The default is Count - which would just be the number of games played, not the W/L record. There’s no way to reference the Pivot in a formula in a pivot table, it’s just generic summaries like “Percent Empty”

Also, kudos on the team names. :slightly_smiling_face:

While you’re correct that the pivot table data can’t be referenced in another field, which is a bummer… it is actually showing you total wins vs team x and total losses vs team x, and thus a W/L record in total and a W/L record per team played. It’s just not formatted quite as you’d like it to be, perhaps.

If you look at “Thrips” in the horizontal plane, you see a number of wins against each team they played going across it horizontally:

• 1 win against Panthers
• 1 win against Aardvarks
• 1 win against Mole Rats
• 3 wins total

And then if you look at “Thrips” in the vertical plane, you see a number of losses against each team they played going down it vertically:

• 1 loss against Mole Rats
• 1 loss against Candrius
• 2 losses total

Total W/L record - 3/2. Record against Mole Rats - 1/1.

I’m pretty sure that’s what you were asking for, right?

yeah you are right, it’s pulling the data you would want, just in an incredibly hard to digest format.

The database function would just be allowing you to reference a count against a combination of elements, instead of a single element (if “team a” appears in row A and “team b” appears in row b then count). But that’s not available in airtable afaik.

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:

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:

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!