I have a table PEOPLE with columns ID and AGE and other columns that are not relevant to this question. I can have two rows with the same ID (this happens if data of one oth the non relevant columns is modified, I duplicate the row in order to keep history. Unfortunately I cannot modify this behavior and put the ages into another table.)
What I want to achieve is to count the number of people by age only if the ID appears for the first time. I tried to do it as follows (I use countifs because I'm checking multiple columns, but only AGE is relevant here) :
AGE_DISTRIBUTION[AGE] contains the age values.
This works fine as long as I don't have a duplicate ID, however as I duplicate rows, the formula counts these duplicates. How can I avoid this (i.e. only counting the first occurance of each ID)
This results in something like this :
PEOPLE Table :
| ID | Age | ... | | 1 | 32 | ... | | 2 | 42 | ... | | 1 | 32 | ... |
AGE_DISTRIBUTION Table :
| Age | Count | | ... | ... | | 32 | 2 | <-- here I should have 1 and not 2 | ... | ... | | 42 | 1 | | ... | ... |
Thank you in advance for your help.
side note: I cannot use pivot tables.