Counting Sub columns
Hello,
I have a sheet that has category 1, then sub category 1, then subsub 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.
Answers

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")
Thanks,
Aravind GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!