Pivotby using filter

[jon999]

Hi

Is there a way to use pivotby and filter based on the total if it is greater than a specified number?

Sample data:

Client	Amount
A	10
A	20
A	15
B	50
B	80
B	30

I want to return any client where the total amount for each client exceeds 100. Therefore, the result will only show client B.

Thanks

 

[hagia_sofia]

Hello, one option could be:

=GROUPBY(A2:A7,B2:B7,SUM,,0,,SUMIFS(B2:B7,A2:A7,A2:A7)>100)

 

[Fluff]

It sounds like you have 365.
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[alansidman]

An alternative means with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Client"}, {{"Total", each List.Sum([Amount]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Total] > 99)
in
    #"Filtered Rows"

 

[jon999]

Thanks both for your answers.

 

[alansidman]

Thanks for the feedback and updating your profile as Fluff asked.