2

I am using an ARRAYFORMULA to extract unique values from a list of names and total up a second column for each of those names.

Here is some example data and the ARRAYFORMULA I am using

Example Data

ARRAYFORMULA in cell E3

=ARRAYFORMULA({{unique(B3:B11)},{sumif(B3:B11, (unique(B3:B11)),C3:C11)}})

What I would like to do is filter out those names where the total number of tokens is zero - i.e. I would like Peter to be filtered out of the list because he has a net total of zero Tokens.

[I'd also like to have Peter, and any others, displayed in a separate list, but I guess if you can help me crack this first part I can help myself crack the second part]

Is there a way I can achieve what I want with a single ARRAYFORMULA? Is there another (better) way to achieve the same result?

I've bashed my head against this for a while so would be grateful for any help

player0
  • 124,011
  • 12
  • 67
  • 124
FlereImsaho
  • 23
  • 1
  • 2

2 Answers2

1

use:

=QUERY(QUERY({B3:C}; 
 "select Col1,sum(Col2) 
  where Col1 is not null 
  group by Col1 
  label sum(Col1)''"); 
 "where Col2 is not null"; )
player0
  • 124,011
  • 12
  • 67
  • 124
0

Query and Filter

An image of a two tables, raw input and desired output

=FILTER(QUERY(A2:B10,"select A, SUM(B) group by A"),QUERY(A2:B10,"select SUM(B) group by A") > 0)

You don't need an array formula. The FILTER on the outside of the QUERY takes care of our zero token problem. And the QUERY selects column A and SUM of B and returns the proper names and sums, so that there are no repeating names. We need a second identical QUERY for the FILTER to compare 0s against, Because our QUERY doesn't exist outside of the formula. Every reference you make to the QUERY requires you to perform the QUERY again.

Gabriel Pierce
  • 396
  • 3
  • 11
  • Worked perfectly thanks, and thanks also for the explanation - I had an inkling that I needed to make some kind of 'recursive' call to the list created – FlereImsaho Jan 05 '22 at 21:12