Counting Sub columns

kateflowers

Hello,

I have a sheet that has category 1, then sub category 1, then sub-sub category 1. An example of the way it is set up is:

Lunch (no indent)

-Meats (one indent)

--turkey (two indents)

Dinner (no indent)

-meats (one indent)

--turkey (two indents)

(hopefully you understand this example of how my data is set up)

I am looking for a formula that tells me how many times i have turkey as a meat for lunch, rather than the total amount of times i ate turkey (lunch, dinner, etc). I have tried doing count functions, but it ends up giving me back the total amount of times i had turkey, rather than the total amount i had turkey for specifically lunch or specifically dinner. If anyone can help to explain how you can correctly find this/formulate it, it would be much appreciated.

AravindGP

Hi @kateflowers

You can add a helper column and modify your formula to get the desired result. Add a column named Parent (or whatever you feel like). Use the formula "=IFERROR(INDEX(ANCESTORS([Primary Column]@row), 1) + " | " + PARENT([Primary Column]@row), ""). This formula will add the name of the ancestor of the item and then the parent. For example, against Lunch, this field will be blank. But for Meats, it will show as Lunch | Lunch and for Turkey under Lunch and Meats it will show as Lunch | Meats. You can replace the separator with any value such as a comma or a slash.

You can then use Countifs formula like this "=COUNTIFS([Primary column]:[Primary column], "Turkey", [Parent]:[Parent], "Lunch | Meats")