Conditional Counting if Multiple Conditions Met

[Nabeel_Hyatt]

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.

[Nabeel_Hyatt]

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.

[Elias_Gomez_Sai]

What are your Fields in the Games table?

[Nabeel_Hyatt]

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.

[W_Vann_Hall]

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

[Jeremy_Oglesby]

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

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

[W_Vann_Hall]

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?

[Nabeel_Hyatt]

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:

[Jeremy_Oglesby]

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?

[Nabeel_Hyatt]

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.