I have an excel spreadsheet with one column for ingredients and one column for recipes. (see on the left in the picture below). I would like to create a matrix with all ingredients (as seen in the picture on the right) that shows how many times each pair of ingredients appears in the same recipes.

input and wanted output

So far I tried it with combinations of VLOOKUP and COUNTIFS but I did not get a sufficient solution.

The command I am looking for should be something like: "For every time ingredient-x (Gin) appears in a recipe, check which other ingredients appear in the same recipe. And count these co-appearances in the matrix."

My limited knowledge tells me that this is only possible with an algorithm written in VBA because I'd have to tell it to "for every ingredient count home often every other ingredient appears in the same recipe.

I would appreciate your help, and since this is my first question on Superuser please don't hesitate to give me feedback on how to improve my asking!

  • Welcome to SuperUser. You can put VLOOKUP and COUNTIFS inside code samples to make them stand out. – RamonRobben Nov 19 '18 at 10:58

EDIT: A better solution that doesn't require an extra column

You can use the functionCOUNTIFS to check if your first range is equal to something and if your second range is equal to something.

I used the function: =COUNTIFS(B3:B8, "Tonic Water", C3:C8, "Gin") To count how many combinations of Tonic Water + Gin where in the two ranges.

You could also see the function like this: =COUNTIFS(RANGE1, RECIPE, RANGE2, INGREDIENT)

Here is an image explaining it a bit better:


I checked your "input and wanted output" image and the data in the output table doesn't match up with whats in the input ranges. However if I look at your question it seems that my answer will work for that.

EDIT: My comment answer:

Put the ingredients on the left side and the ingredients at the top. Then use COUNTIFS to check how many times an ingredient is in a recipe. From this table you can easily check if and what ingredients share a recipe.

Image example: enter image description here

In the image you can see Ginger Ale and Vodka both share the recipe Moscow Mule

  • Thank you, but sadly this is not the solution I was looking for. Now it counts how many times the recipe name matches the name of an ingredient. How many times do two ingredients appear with the same recipe name in the column to their left? My guess that I'll have to use excel-vba because has to repeat one process (how many times do two ingredients appear in the same recipe) over and over again. – djn Nov 19 '18 at 12:44
  • You can make an extra table where you put all the Recipes on the left and then all the Ingredients on the top and then use COUNTIF to check if an ingredient is in a recipe. From this table you might be able to extract the information you need. – RamonRobben Nov 19 '18 at 15:01
  • perfect! Thank you! This worked – djn Nov 21 '18 at 13:25
  • @djn Glad it worked for you. Don't forget to accept this answer if it solved your question. – RamonRobben Nov 21 '18 at 13:40

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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