# Counting how often two different values appear with the same value in the neighbouring cell

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.

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 function`COUNTIFS` 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.

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.