Skip to main content
Web Applications

Return to Answer

corrected typos introduced when I added the LAMBDA formula and modified the named ranges. Updated images.
Source Link
Blindspots ♦
Blindspots ♦
  • 7.5k
  • 5
  • 20
  • 38

COUNTIF & FILTER

  • The COUNTIF function returns a conditional count across a range;
    =COUNTIF(range, criterion)
     
  • The FILTER function returns a filtered version of the source range, returning only rows or columns that meet the specified conditions.
    =FILTER(range, condition1, [condition2, ...])
     
  • By combining COUNTIF & FILTER one can FILTER the data across all days based on a menu item, then use COUNTIF to count occurrences of a name in the filtered data.

Named Ranges in Formulas

The formula can be adapted for a LAMBDA function

name range note
_mealdata $G$2F2:$P$2O2 Range contains menu item for that dayDaily meals data entries
_namedata $G$2F3:$P$8O7 Range contains unique list of namesDaily names data entries
_namelist $A$3A2:$P$8A7 Range contains names listed each dayList of names for filtering data
_meallist $A$2B1:$P$8D1 Range contains a unique list of menu itemsList of meals for filtering data

First Formula

Formula uses named ranges to make the code easier to manage.

=COUNTIF(FILTER(_names_namedata, _meals=C$2_mealdata=B$1), $B3$A2)

LAMBDA Function

=BYROW(_namelist, LAMBDA(n, MAP(_meallist, LAMBDA(m, COUNTIF(FILTER(_namedata,_mealdata=m),n)))))

COUNTIF & FILTER

  • The COUNTIF function returns a conditional count across a range;
    =COUNTIF(range, criterion)
     
  • The FILTER function returns a filtered version of the source range, returning only rows or columns that meet the specified conditions.
    =FILTER(range, condition1, [condition2, ...])
     
  • By combining COUNTIF & FILTER one can FILTER the data across all days based on a menu item, then use COUNTIF to count occurrences of a name in the filtered data.

Named Ranges in Formulas

The formula can be adapted for a LAMBDA function

name range note
_mealdata $G$2:$P$2 Range contains menu item for that day
_namedata $G$2:$P$8 Range contains unique list of names
_namelist $A$3:$P$8 Range contains names listed each day
_meallist $A$2:$P$8 Range contains a unique list of menu items

First Formula

Formula uses named ranges to make the code easier to manage.

=COUNTIF(FILTER(_names, _meals=C$2), $B3)

LAMBDA Function

=BYROW(_namelist, LAMBDA(n, MAP(_meallist, LAMBDA(m, COUNTIF(FILTER(_namedata,_mealdata=m),n)))))

COUNTIF & FILTER

  • The COUNTIF function returns a conditional count across a range;
    =COUNTIF(range, criterion)
     
  • The FILTER function returns a filtered version of the source range, returning only rows or columns that meet the specified conditions.
    =FILTER(range, condition1, [condition2, ...])
     
  • By combining COUNTIF & FILTER one can FILTER the data across all days based on a menu item, then use COUNTIF to count occurrences of a name in the filtered data.

Named Ranges in Formulas

The formula can be adapted for a LAMBDA function

name range note
_mealdata F2:O2 Daily meals data entries
_namedata F3:O7 Daily names data entries
_namelist A2:A7 List of names for filtering data
_meallist B1:D1 List of meals for filtering data

First Formula

Formula uses named ranges to make the code easier to manage.

=COUNTIF(FILTER(_namedata, _mealdata=B$1), $A2)

LAMBDA Function

=BYROW(_namelist, LAMBDA(n, MAP(_meallist, LAMBDA(m, COUNTIF(FILTER(_namedata,_mealdata=m),n)))))
added 229 characters in body
Source Link
Blindspots ♦
Blindspots ♦
  • 7.5k
  • 5
  • 20
  • 38

COUNTIF & FILTER

  • The COUNTIF function returns a conditional count across a range;
    =COUNTIF(range, criterion)
     
  • The FILTER function returns a filtered version of the source range, returning only rows or columns that meet the specified conditions.
    =FILTER(range, condition1, [condition2, ...])
     
  • By combining COUNTIF & FILTER one can FILTER the data across all days based on a menu item, then use COUNTIF to count occurrences of a name in the filtered data.

Named Ranges in Formulas

The formula can be adapted for a LAMBDA function

name range note
_mealdata $G$2:$P$2 Range contains menu item for that day
_namedata $G$2:$P$8 Range contains unique list of names
_namelist $A$3:$P$8 Range contains names listed each day
_meallist $A$2:$P$8 Range contains a unique list of paswordsmenu items

First Formula

Formula uses named ranges to make the code easier to manage.

=COUNTIF(FILTER(_names, _meals=C$2), $B3)

LAMBDA Function

=BYROW(_namelist, LAMBDA(n, MAP(_meallist, LAMBDA(m, COUNTIF(FILTER(_namedata,_mealdata=m),n)))))

COUNTIF & FILTER

  • The COUNTIF function returns a conditional count across a range;
    =COUNTIF(range, criterion)
     
  • The FILTER function returns a filtered version of the source range, returning only rows or columns that meet the specified conditions.
    =FILTER(range, condition1, [condition2, ...])
     
  • By combining COUNTIF & FILTER one can FILTER the data across all days based on a menu item, then use COUNTIF to count occurrences of a name in the filtered data.

Named Ranges in Formulas

name range note
_mealdata $G$2:$P$2 Range contains menu item for that day
_namedata $G$2:$P$8 Range contains unique list of names
_namelist $A$3:$P$8 Range contains names listed each day
_meallist $A$2:$P$8 Range contains a unique list of paswords

First Formula

Formula uses named ranges to make the code easier to manage.

=COUNTIF(FILTER(_names, _meals=C$2), $B3)

LAMBDA Function

COUNTIF & FILTER

  • The COUNTIF function returns a conditional count across a range;
    =COUNTIF(range, criterion)
     
  • The FILTER function returns a filtered version of the source range, returning only rows or columns that meet the specified conditions.
    =FILTER(range, condition1, [condition2, ...])
     
  • By combining COUNTIF & FILTER one can FILTER the data across all days based on a menu item, then use COUNTIF to count occurrences of a name in the filtered data.

Named Ranges in Formulas

The formula can be adapted for a LAMBDA function

name range note
_mealdata $G$2:$P$2 Range contains menu item for that day
_namedata $G$2:$P$8 Range contains unique list of names
_namelist $A$3:$P$8 Range contains names listed each day
_meallist $A$2:$P$8 Range contains a unique list of menu items

First Formula

Formula uses named ranges to make the code easier to manage.

=COUNTIF(FILTER(_names, _meals=C$2), $B3)

LAMBDA Function

=BYROW(_namelist, LAMBDA(n, MAP(_meallist, LAMBDA(m, COUNTIF(FILTER(_namedata,_mealdata=m),n)))))
added 229 characters in body
Source Link
Blindspots ♦
Blindspots ♦
  • 7.5k
  • 5
  • 20
  • 38

COUNTIF & FILTER

  • The COUNTIF function returns a conditional count across a range;
    =COUNTIF(range, criterion)
     
  • The FILTER function returns a filtered version of the source range, returning only rows or columns that meet the specified conditions.
    =FILTER(range, condition1, [condition2, ...])
     
  • By combining COUNTIF & FILTER one can FILTER the data across all days based on a menu item, then use COUNTIF to count occurrences of a name in the filtered data.

Example Formula

Formula uses named ranges to make the code easy to read

=COUNTIF(FILTER(_names, _meals=C$2), $B3)

Named Ranges in Formulas

name range note
_meals_mealdata $G$2:$P$2 Range contains each day's menu item for that day
_names_namedata $G$3$G$2:$P$8Range contains unique list of names
_namelist$A$3:$P$8 Range contains names listed each day
_meallist$A$2:$P$8Range contains a unique list of paswords

First Formula

Formula uses named ranges to make the code easier to manage.

=COUNTIF(FILTER(_names, _meals=C$2), $B3)

LAMBDA Function

COUNTIF & FILTER

  • The COUNTIF function returns a conditional count across a range;
    =COUNTIF(range, criterion)
     
  • The FILTER function returns a filtered version of the source range, returning only rows or columns that meet the specified conditions.
    =FILTER(range, condition1, [condition2, ...])
     
  • By combining COUNTIF & FILTER one can FILTER the data across all days based on a menu item, then use COUNTIF to count occurrences of a name in the filtered data.

Example Formula

Formula uses named ranges to make the code easy to read

=COUNTIF(FILTER(_names, _meals=C$2), $B3)

Named Ranges

name range note
_meals $G$2:$P$2 Range contains each day's menu item
_names $G$3:$P$8 Range contains names listed each day

COUNTIF & FILTER

  • The COUNTIF function returns a conditional count across a range;
    =COUNTIF(range, criterion)
     
  • The FILTER function returns a filtered version of the source range, returning only rows or columns that meet the specified conditions.
    =FILTER(range, condition1, [condition2, ...])
     
  • By combining COUNTIF & FILTER one can FILTER the data across all days based on a menu item, then use COUNTIF to count occurrences of a name in the filtered data.

Named Ranges in Formulas

name range note
_mealdata $G$2:$P$2 Range contains menu item for that day
_namedata $G$2:$P$8Range contains unique list of names
_namelist$A$3:$P$8 Range contains names listed each day
_meallist$A$2:$P$8Range contains a unique list of paswords

First Formula

Formula uses named ranges to make the code easier to manage.

=COUNTIF(FILTER(_names, _meals=C$2), $B3)

LAMBDA Function

Source Link
Blindspots ♦
Blindspots ♦
  • 7.5k
  • 5
  • 20
  • 38