SOLVED

When the numeric value to SUM is in column X and the list to detect UNIQUE is column Y

Occasional Contributor

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!

5 Replies

Re: When the numeric value to SUM is in column X and the list to detect UNIQUE is column Y

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.

Re: When the numeric value to SUM is in column X and the list to detect UNIQUE is column Y

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 more
best response confirmed by allyreckerman (Microsoft)
Solution

Re: When the numeric value to SUM is in column X and the list to detect UNIQUE is column Y

See the attached version. I used a helper column, and I added two solutions: one formula-based and the other a pivot table.

Re: When the numeric value to SUM is in column X and the list to detect UNIQUE is column Y

Thanks!
This solves it definitely!
How could I calculate everything in one cell?
This would create repeated calculated values, I know but with the no-simplified version this makes sense.

Re: When the numeric value to SUM is in column X and the list to detect UNIQUE is column Y

Here is a solution without a helper column.