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.

  • You could try using =countifs() – eirikdaude May 15 '15 at 22:06

Your formula in Sheet2!K2 would be:


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

  • Hi, That's a good start, but it does not give unique results - if the person matches for multiple allergens, it counts them twice – thelawnet May 16 '15 at 2:00
  • There I think I've got it! Let me know if this works for you? – maciej May 19 '15 at 4:35
  • (I updated the answer above) – maciej May 22 '15 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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