arrayformula + sumif to autofill / expanding result

user_18883157 0
Viewed 105

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

sumifs array-formulas google-sheets-formula google-sheets
2 Answers

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))))}
user_56326290

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

Change F2 to F2:F22.

user_178873010
Related