CODEWITHSUNDEEP
excelexcel-formula
René Martínez
René Martínez

Reputation: 309

Excel: How to SUM without duplicating data in a filtered table?

I have this table that refers to subway stations and surrounding population, I need to sum the population without duplicating even when data is filtered.

enter image description here

Subtotal gives me 74,536 when in reality, we are talking about 46,601 individuals. Now, the problem I have is when data is filtered by stations

enter image description here

It keeps showing 46,601 when thats not the case. How can I solve this?

FYI, the formula I´m using in Col B:=SUMPRODUCT([Col B]/COUNTIF([Col B],[Col B]))

Thank you so much for your time.

Upvotes: 0

Views: 57

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60484

You can do this with a array formula:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET([ColB],ROW([ColB])-ROW(B2),,1)),[ColB]),[ColB]),[ColB]))

Since this is an array formula, you need to "confirm" it by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula as observed in the formula bar

enter image description here

I suggest using the Formula Evaluator tool to understand how this works.

The SUBTOTAL(2,OFFSET... returns an array of {1;1;1;0;0;0...} where 1 represents a visible row.

We use that to return an array of the values in the visible rows.

The FREQUENCY function, where data and bins are the same will then return an array where the first of a duplicated value will return a count of that value, and the other instances will return a 0.

We use that to return only the visible, non-duplicate instances and sum them.

Upvotes: 1

Related Questions