2

I have sheet1 with a list of people and their allergies/dietary requirements. There are nine allergies/dietary needs, and each person has 1 or more allergies. Each requirement is a column, each person in a row.

If the person has that allergy, the cell contains '1', otherwise blank

In sheet2, I have a list of foods (one food per row - column A), and the possible allergens they might contain (one allergen per column - the same column headings as sheet1, Columns B - J). Again, if the food contains the allergen the cell contains '1', otherwise blank.

What I want to do, is in Column K of Sheet 2, calculate the number of unique people that cannot eat the food for any reason.

thelawnet
  • 91

1 Answers1

1

Your formula in Sheet2!K2 would be:

=SUM(--(MMULT(--Sheet1!$B$2:$J$9,TRANSPOSE(--B2:J2))>0))

This is an array function, so you have to press Ctrl+Shift+Enter when entering it. Then fill that down column K.

maciej
  • 131