I would like to make a count based on fulfilling two conditions in a column across a range of columns. For the sake of easily explaining things, I have decided to make a table of names and foods, I've added days as well, but they are not relevant and here only to help me describe my problem.

Is there a way, to count f.e. how many times Antony has eaten pizza on different days (or better said, Antony and pizza have occured in the same column across a range) and do that for hamburger and hot dog as well. Repeat that for Derek, Iva and others.

I have tried mapping numbers and implementing lambda, but I am really not experienced with it and all my attempts of comming up with something that will work have ended in vain.

Hope my explanation is clear, I would appreciate if anyone could help me solve my problem.

*The numbers in "Data" were added manually.

enter image description here


3 Answers 3



  • 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)))))
  • 1
    Perfect, works like a charm, thank you!
    – Dots
    Commented Feb 27, 2023 at 14:43

I don’t know if this option would be easier, but for me the formula


placed in cell B4 and then stretched over the entire range B4:D9 gave the same result as that of our respected colleagues Blind Spots and doubleunary

  • Thank you ever so much, it works as desired as well!
    – Dots
    Commented Feb 27, 2023 at 14:40

To collect the data, use string concatenation, flatten() and split() in an array formula. To aggregate and pivot the data, use query() with a pivot clause, like this:

    split( flatten(E4:N & "→" & E3:N3), "→", false, false), 
    "select Col1, count(Col1) 
     where Col1 is not null 
     group by Col1 
     pivot Col2", 

The formula above is an array formula that will create the whole result table, complete with headers, in one go.

The results look like this:

Results Data
Hamburger Hotdog Pizza Pizza Hamburger Hotdog Pizza Pizza Hotdog
Antony 1 1 3 Antony Mia Iva Antony Iva Josh
Derek 1 3 Derek Antony John Derek John Mia
Iva 1 3 Iva Derek Josh Iva Mia Antony
John 1 3 John John Antony
Josh 2 2 Josh Josh Derek
Mia 1 1 1

See query(), split() and flatten().

  • Thank you for your contribution, it works well and it gave me another viewpoint on how to manipulate data.
    – Dots
    Commented Feb 27, 2023 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.