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) :

=COUNTIFS(PEOPLE[AGE];AGE_DISTRIBUTION[AGE])

Where 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.

1 Answers

0
JvdV On

Im not quite sure if this is what you meant but:

enter image description here

Formula used translates to:

{=SUM(--(FREQUENCY(IF(Table1[AGE]=[@AGE],Table1[ID]),Table1[ID])>0))}

Notice that this is an array formula and should be entered through CtrlShiftEnter

And upon expending:

enter image description here