Home > OS >  arrayformula sumif to autofill / expanding result
arrayformula sumif to autofill / expanding result

Time:04-21

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

CodePudding user response:

use:

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

for removing zeros use:

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

CodePudding user response:

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

Change F2 to F2:F22.

  • Related