Forum Discussion
When the numeric value to SUM is in column X and the list to detect UNIQUE is column Y
Hello, I have a dataset that is essentially this. What I need to do is to count the total number of employees by Country (not City). I'm having a hard time finding the correct formula for this. It is not shown in this example dataset, but there are some cases that the city has the same name but is a different country.
Basically, I want to identify the unique values from column B (which will have a correspondent value in column C which might or might not be repeated), to then SUM the values from column C and my range of values is the filtered values for each country from column A.
I was thinking two nested UNIQUE formulas or maybe FILTER, but I just can't get the correct syntax, Someone can think of a solution? It will be much appreciated!
See the attached version. I used a helper column, and I added two solutions: one formula-based and the other a pivot table.
5 Replies
I'm confused.
You write "count the total number of employees by Country (not City)". If that is correct, it does not matter whether there are cities with the same name in different countries.
- Yes, you are right to be confused haha. I just realized I didn't specify that the values from column B are repeated.
This dataset is a simplified version of my real dataset, and I have more fields in further columns, but for simplicity, we can assume that the values from column B are repeated (having always the same value on C per row) but I need to sum that value only one time.
I hope this clarifies it moreSee the attached version. I used a helper column, and I added two solutions: one formula-based and the other a pivot table.
