Count occurences

Question

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.

Answer 1

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)))))

Answer 2

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

Answer 3

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().