I thought I had a smart idea, but it apears to be harder than expected:
For a billing system I need the amount of adults and the ages of the childeren. Kids pay €1 per age-year (e.g. a 4 year old kid pays €4 for their meal, and a 10 year old kid €10) But to make it harder, the 1 year old kid eats free.
I have a single cell where the kids ages are given, for instance: 3,6,6,9 (one 3 year old, 2 six year olds and a 9 year old)
I wanted to do someting like:
countif({3,6,6,9},"=2")*2 and so on for each value up to 10. So in essence countif("{"&A2&"}","=2")*2, but obviously this fails.
But searching the net, I have not found an easy solution for this. And it has indeed to be a single entry cell (as we can never determin in advance how many kids will ultimatly be the max + user friendlyness), and a single cell with the outcome (on the bill itself).
Any smart ideas how to solve this? Thanks!