Spreadsheet help with ARRAYFORMULA returning zeros

Ask for Help
1

Hi,

I’m working on an app and it’s using some ARRAYFORMULA’s to reference data on different sheets, etc. The issue I’m having is that whenever someone “Adds” an entry, the ARRAYFORMULA seems to add a “0” row as well as the actual data to the other sheet.

My spreadsheet is here: Copy of SH Wellness - Google Sheets

The sheet in question is the “My Points” tab, which should take the values in the “My Activities” tab and sum them all up, then rank them according to highest total points. For some reason, when I add new rows (through to corresponding app), the formula in column B in “My Points” adds a new “0” row at the same time. This wouldn’t be an issue, but with the 500 row limit, I want to try to prevent this.

Any advice? Thanks in advance.

2

Try this formula in cell B1 of “My Points”

={"Points Per User";Arrayformula(IF(ISBLANK(A2:A3),"", SUMIF('My Activities'!$A$2:$A,A2:A,'My Activities'!$D$2:$D)))}

You could however use this formula in A1 instead of what you have to do it all in one shot.

=SORT(QUERY('My Activities'!A1:D,"Select A, Sum(D) where A is not null group by A",1),2,false)
1 Like
3

Mind = Blown

Thanks so much! That formula is bananas… I’ve never used or heard of the QUERY function… that’s wild stuff.

4

One way to hide zeros would be to custom format the number like this #;-#;""

Ref: https://infoinspired.com/google-docs/spreadsheet/remove-0-values-in-google-sheets/