1

MY DATA

I have the following sheet of data, data:

enter image description here

PERSON TEAM Lookup COUNT
FOO A Y 3
BAR A N 1
BAZ A N 4
FOO B N 1
BAR B N 2
BAZ B Y 5
FOO C N 3
BAR C Y 1
BAZ C N 2

MY INTENDED FINAL RESULT

I want to GROUP BY TEAM, SUM COUNT, and match that back to PERSON WHERE LOOKUP is equal to Y, like so:

enter image description here

WHAT I HAVE TRIED

I used the =QUERY() function to obtain the SUM of COUNT by TEAM:

enter image description here

This gets me most of the way there. But I can't quite find a way to now bring the PERSON from each TEAM WHERE LOOKUP is equal to Y. I thought of trying a VLOOKUP on TEAM, but I only want one specific result from each team, again, where LOOKUP is equal to Y. Can I do some sort of VLOOKUP with a WHERE condition? Or perhaps is there a way where this can be done in one step instead of two?

Improve this question
3
  • Can you try =QUERY(A2:D10, "SELECT A, B, SUM(D) WHERE C = 'Y' GROUP BY A, B") to see if that works? The QUERY function has a WHERE condition that could GROUP BY TEAM, SUM COUNT, and match that back to PERSON WHERE LOOKUP is equal to Y. Commented Sep 5 at 17:38
  • This does not return the intended result. It returns the COUNT value for PERSON WHERE LOOKUP is equal to Y. I need the to return the SUM of TEAM for PERSON WHERE LOOKUP is equal to Y as stated in the OP. Commented Sep 5 at 17:52
  • 1
    Apologies, I see what you mean now. Would =ARRAYFORMULA({FILTER(A2:B, C2:C = "Y"), SUMIF(B2:B, FILTER(B2:B, C2:C="Y"), D2:D)}) work with what you'd like to do? Commented Sep 5 at 19:04

4 Answers 4

Reset to default
3 
=hstack(filter(A2:B,C2:C="Y")
       ,arrayformula(sumif(B2:B,tocol(unique(B2:B),1),D2:D)))

works.

enter image description here

------------

What we did was 2 parts: 1) filter PERSON, TEAM block by Lookup column; 2) aggregate COUNT column by TEAM column.

We can achieve either part with any number of methods. But to simplify the combining of the 2 parts, we should ideally preserve the same ordering in their outputs.

In the above example, filter and unique both result in the same ordering of outputs, which is the order of appearance in the original column. sumif re-uses the ordering from unique and thus the two parts have the same ordering. And combining is thus simply hstack.

tocol(_,1) use is required because unique includes blank cells.

-------------

Above is assuming the Lookup column is "clean": each Team only has 1 corresponding Lookup = "Y", which is the example used in OP. If the column is not clean, we would have multiple PERSONs passing the Lookup="Y" filter. In that case, a further _lookup (as in a function call of sort, to be distinct from OP's Lookup column) is required at the combining stage. Alternatively, we can process the data so it is clean. In the example below, we combine the cleaning and filtering by Lookup="Y" together in order to arrive at a convenient call of vlookup.

For example, we can replace the naive filter by Lookup="Y", which was

filter(A2:B,C2:C="Y")

with

let(selected,filter({B2:C,A2:B},C2:C="Y")
   ,uni,unique(choosecols(selected,1,2))
   ,arrayformula(vlookup(choosecols(uni,1),selected,{3,4})))

where we first filter data columns by Lookup = "Y", then select unique combination of TEAM and Lookup, and at the end, for each of such unique combination, we use vlookup to query the first occurrence of PERSON which passed the earlier filter.

The aggregate summation and the simple combining by hstack would remain unchanged. For clarity, the full formula would be

=let(selected,filter({B2:C,A2:B},C2:C="Y")
    ,uni,unique(choosecols(selected,1,2))
    ,arrayformula(hstack(vlookup(choosecols(uni,1),selected,{3,4})
                        ,sumif(B2:B,tocol(unique(B2:B),1),D2:D))))

Alternatively, we can also note that unique TEAM + Lookup="Y" actually implies unique TEAM because the Lookup column only has 1 value after filter. That saves a unique call as follows, which can be advantageous for a large database.

=let(selected,filter({B2:C,A2:B},C2:C="Y")
    ,uni,wraprows(tocol(unique(choosecols(selected,1,2)),1),2)
    ,arrayformula(hstack(vlookup(choosecols(uni,1),selected,{3,4})
                        ,sumif(B2:B,choosecols(uni,1),D2:D))))

Note that we chose hstack as a function call as opposed to {} so we do not have to balance the brackets manually at the end.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2 
=BYROW(FILTER(A2:B10,C2:C10="Y"),LAMBDA(_rw,{_rw,SUMIF(B:B,INDEX(_rw,1,2),D:D)}))

First we filter the rows of interest (Lookup = Y), and go over the outcome row-by-row using some LAMBDA helper function called BYROW. Here we simply stack the sumif result of the team to the row. Kind of a HSTACK.

Improve this answer

2 Comments

Come on people. Really, is this what SO has become? Excel and GS tags used to be interesting and fun to answer. These days you'd get downvoted (without any feedback) for trying to be helpfull.
Nice! Note though that the question leaves it unclear whether there's always exactly one Y row per team.
2

Use query() to aggregate the data, and filter() to look up the person per the criteria you specified, like this:

=let(
  data, query(A1:D, "select B, sum(D) where C is not null group by B label sum(D) 'TOTAL' ", 1),
  names, byrow(data, lambda(row,
    ifna(single(filter(A2:A, B2:B = choosecols(row, 1), C2:C = "Y")), A1)
  )),
  hstack(names, data)
)

Results:

PERSON TEAM TOTAL
FOO A 8
BAZ B 8
BAR C 6

The formula will continue working also in the event there are more than one Y rows per team. Another way to do the same, sans headers, is with sortn():

=sortn(filter(hstack(A2:B, sumif(B2:B, B2:B, D2:D)), C2:C = "Y"), 9^9, 2, 2, 1)

Hat tip to bricks96.

See let(), query(), byrow(), ifna() filter(), choosecols(), hstack() and sumif().

Improve this answer

3 Comments

You can include the sumif as part of the data that you're filtering, since the FILTER will array-enable the SUMIF. This also continues to work when there are multiple Y per team. =filter({A:B,sumif(B:B,B:B,D:D)},C:C="Y")
Thanks, that's indeed a simpler way of doing the same. Edited the answer. Note that this formulation won't work correctly when there is more than one Y row per team — the aggregate total gets larger than the total in data.
I see. I assumed they wanted all Persons with Y to appear, but if it's only one per team that can be displayed, we could use =sort(filter({A:B,sumif(B:B,B:B,D:D)},C:C="Y"),9^9,2,2,1) to limit the results to one person per team.
1

You may try:

=ARRAYFORMULA(UNIQUE({FILTER(A2:B, C2:C="Y"), SUMIF(B2:B, FILTER(B2:B, C2:C="Y"), D2:D)}))
  • The formula uses UNIQUE({FILTER(A2:B, C2:C="Y") to select the data in Columns A and B that has Column C as Y.
  • It then uses SUMIF(B2:B, FILTER(B2:B, C2:C="Y"), D2:D) to sum the values in Column D for each unique value of Column B that has Column C as Y.
  • Afterwards, the output is then enclosed with ARRAYFORMULA and the curly braces {} to combine the results into a single, two-column array.

The formula can also be written as:

=BYROW(UNIQUE(FILTER(A2:B, C2:C="Y")), LAMBDA(x, {x, SUMIF(B2:B, INDEX(x, 1, 2), D2:D)}))

=LET(a, UNIQUE(FILTER(A2:B, C2:C="Y")), BYROW(a, LAMBDA(x, {x, SUMIF(B2:B, INDEX(x, 1, 2), D2:D)})))

Note: The formulas count each unique item so long as the LOOKUP is equal to Y. If a person is in two teams that both have Y, it'll create a new entry with their name equal to the sum of the teams they're on.

To return one person per team, even if there are multiple Ys, you may also try these:

=BYROW(UNIQUE(FILTER(B2:B, C2:C="Y")), LAMBDA(x, {INDEX(FILTER(A2:A, B2:B=x, C2:C="Y"), 1), x, SUMIF(B2:B, x, D2:D)}))

=LET(a, FILTER(B2:B, C2:C="Y"), REDUCE({"PERSON", "TEAM", "COUNT"}, UNIQUE(a), LAMBDA(x, y, {x; XLOOKUP(y, a, FILTER(A2:A, C2:C="Y")), y, SUMIF(B2:B, y, D2:D)})))
  • The formula gets the first person of the team that is equal to Y and continues to sum the values in Column D.

OUTPUT

PERSON TEAM TOTAL
FOO A 8
BAZ B 8
BAR C 6

REFERENCES

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.