0

In Excel for web, I use certain functions like UNIQUE and SUMIF over large ranges. Often times these ranges contain blank values, and UNIQUE will output a 0, and SUMIF too. How can I filter these?

I've tried using the FILTER function, but it doesn't seem like that's meant for arrays.

With the following example I get the values that I want, followed by a long list of zeros. Is there any way I can prevent that?

=SUMIF(Transactions!$J$3:$J$1000, $A$2:$A$1000, Transactions!$F$3:$F$1000)
0
1

Replace $A$2:$A$1000 with FILTER($A$2:$A$1000,$A$2:$A$1000<>"")

=SUMIF(Transactions!$J$3:$J$1000, FILTER($A$2:$A$1000,$A$2:$A$1000<>""), Transactions!$F$3:$F$1000)

enter image description here

1
  • Thanks, that worked. So filter the column first, and then feed it into SUMIF and UNIQUE instead of doing the opposite.
    – Erfan
    May 14 '20 at 6:08
1

For example data in column A, pick a cell and enter:

=FILTER(A:A,LEN(A:A))

the spill-down will eliminate the blanks.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.