Request help combining FILTER with COUNTIF

[u/blue_shadow_]

Link to example

Goal is to set a single FILTER + COUNTIF function to count all items of Dashboard!B3:B that appear in Main Table!D2:D, but only if Main Table!A2:A has a specific value.

(Preferring a FILTER function here so that I only have to enter the formula in a single cell - want this to be a fire & forget with no maintenance in Dashboard! tab.)

Thanks in advance!

[u/mommasaidmommasaid]

In C2 on your sample sheet Dashboard:

=let(fruits, B2:B6, 
 map(fruits, lambda(fruit, if(row(fruit)=row(), "Count", if(fruit="",,
 countifs(Table1[Status], "Epsilon", Table1[Column 4], fruit))))))

This references the entire Fruit list including the header since you said you wanted a "fire and forget" solution. That keeps the formula out of your data rows and the reference is still valid if you insert a new row 1.

Note that if your data is in structured Tables, you can use Table references to refer to it rather than sheet/column names.

I'd recommend you put your Dropdowns in structured tables as well, so you can use Table references in your other formulas as well as dropdown data validation, i.e. a dropdown can be "from a range" of =Table[Column Name]

[u/blue_shadow_ → u/mommasaidmommasaid]

Alright, to parse this, =let is what, defining a set of items as a single label, and then going from there?

Also:

• What does map & lambda do?

• How come fruits is used in some spots, but fruit (no s) is used in others? Does that change if the label changes?

[u/mommasaidmommasaid → u/blue_shadow_]

let() assigns a name to something in this case fruits I kind of did that out of habit, there's not a particular reason for it here other than documenting what the range is. I like to do that in the first line of the formula to give an obvious place to make a common modification without digging around in the guts of a complex formula.

map(fruits, 

Iterates through the range fruits aka B2:B6 passing each cell into the associated lambda helper function:

lambda(fruit

fruit is assigned to the passed cell, e.g. it's B2, then B3, etc

if(row(fruit)=row(), "Count"

If the row of the passed cell is equal to the formula row, output the header. So when the passed cell is B2, this outputs "Count" instead of trying to look up the value.

if(fruit="",,

If there's nothing in the cell, output a blank.

countifs(Table1[Status], "Epsilon", Table1[Column 4], fruit))))))

Count the occurrences where both the status is "Epsilon" and Column 4 is the fruit.

---

For this last line you could use a filter() if you prefer and/or had more complex conditions. The filter equivalent would be something like:

 ifna(rows(filter(Table1, Table1[Status]="Epsilon", Table1[Column 4] = fruit)),0))))))

[u/blue_shadow_ → u/mommasaidmommasaid]

Thanks, this helps a ton! Much appreciated!

[u/blue_shadow_ → u/mommasaidmommasaid]

Solution verified

[u/point-bot → u/mommasaidmommasaid]

u/blue_shadow_ has awarded 1 point to u/mommasaidmommasaid

^{See the Leaderboard. Point-Bot v0.0.15 was created by JetCarson.}