# Count occurences

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.

#### 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)))))
``````
• 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

`=SUM(ARRAYFORMULA(IF(\$E\$4:\$J\$9=\$A4,N(\$E\$3:\$J\$3=B\$3),0)))`

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:

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

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