Criteria for non-blank

[huy_le]

Hi guys,

I have a range of values that contains blanks and non-blanks. Blanks are results of v-lookup formula. When I tried to use "<>" as criteria to calculate the number of non-blanks and their revenues, it did not work. Any idea? What should I use instead of "<>"?

[FDibbins]

did you try just plain ol...
=COUNT(A2:A6)

[huy_le]

Hi FDibbins,

I did, that worked to calculate the number, but if I want to use sumif with criteria, what should I use?

[FDibbins]

If you are summing the data, then the blanks wont be included anyway?

[huy_le]

Hi FDibbins,

The column with blanks is criteria, and I'm summing another column based on this column. Sorry if I am confusing you.

[Tony Valko]

Normally, a SUMIF like this should work:

=SUMIF(A2:A6,"<>",B2:B6)

However, in your sample file there is some kind of unseen whitespace characters in cells A3 and A4.

If you clear the contents of those cells the formula above will work.

An alternative that works on the data as-is:

=SUMPRODUCT(--(A2:A6<>""),B2:B6)

[huy_le]

Hi Tony,

Those blanks are the results of v-lookup. That is why I could not just use "<>" as normal.

[huy_le]

Tony,

I think your formula did it. Thanks so much!

[Tony Valko]

Did you try the SUMPRODUCT version and did it work?

[Tony Valko]

Tony,

I think your formula did it. Thanks so much!

You're welcome. Thanks for the feedback!

[huy_le]

Yes, the sumproduct worked.

[Tony Valko]

Good deal. Thanks for the feedback!