CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulassumifs
Lucy Jackson Osborne
Lucy Jackson Osborne

Reputation: 35

arrayformula + sumif to autofill / expanding result

I want to do a simple SUMIF to return the total points for each player, but wanted to use ARRAYFORMULA to autofill down / give expanding results so when more players are added, the SUMIF doesn't need to be dragged down to the new rows.

I know ARRAYFORMULA doesn't work with SUMIFS, but I was sure it worked with SUMIF.

As you can see, the SUMIF is correctly bringing back the total points, but it's not expanding down the column.

Any advice? I was thinking maybe SUMPRODUCT but not sure where to go with that.

screenshot of googledoc

Upvotes: 2

Views: 804

Answers (2)

player0
player0

Reputation: 1

use:

=ARRAYFORMULA(SUMIF(A2:A22, F2:F22, D2:D22))

for removing zeros use:

=ARRAYFORMULA(IFERROR(1/(1/(SUMIF(A2:A22, F2:F22, D2:D22))))

with header:

={"Score"; ARRAYFORMULA(IFERROR(1/(1/(SUMIF(A2:A22, F2:F22, D2:D22))))}

Upvotes: 1

z..
z..

Reputation: 12943

In order for SUMIF to autofill within an arrayformula, the [criterion] parameter must be a range.

Change F2 to F2:F22.

Upvotes: 0

Related Questions