Solved: Advanced Filter with empty cells

[makako]

Hi,

This is not a VBA post, but I hope someone can help me.

I want to filter all cells by an "empty" criteria. I have been trying in the cells, ="=""", ="""", etc. but nothing works, I have attached a workbook and my result should be $450. The data base has multiple lines and in the only one that has TYPE = "" DBSUM should add the value under VALUE. Thanks

[Bob Phillips]

=SUMPRODUCT(--(A2:A10=""),--(B2:B10="Davolio"),C2:C10)

[makako]

Thanks, Im actually using it (the sumproduct function but it actually makes the calculation really slow) but what Im really trying to do is an advanced filter, the dbsum was only to skip all the procedure to execute the filter by cheking the value of the line i should get. Thanks

[Bob Phillips]

How about using * in E3 and then

=SUMIF(B:B,F3,C:C)-(DSUM(A2:C10,C2,E2:G3))

[makako]

I just created a button to show you what Im trying to do, I want to extract the registries that have nothing under a certain title ( type in this case )

[Bob Phillips]

A different tack

[mikerickson]

Put this in the criteria for Type '<>?* and the DSUM returns $450.00

the ' is to make Excel read it as text

<>?* is wildcard speak for "not (any character followed by anything)" i.e. "empty cell"

[makako]

Thanks, <>?* is the kinda' trick i was looking for, works with the advanced filter also. Is there anywhere i can find tips like this?

[mikerickson]

I used the '<>?* on columns that have text only but when I use it on columns with values (money) it doesnt work. I have also tried "<>0", "=0", etc. My problem is I have a value that should be paid and in the next column the paid value, I want to filter the ones that have not paid. Any ideas? thanks

I'm answering your PM in the forum so future searchers can find the response.

Filtering a column of numbers with the criteria >0 showed only the positive numbers for me, hiding the blanks, text and negative number entries.

For a more complicated situation, you might try using the a custom formula criteria.
Eg. if want to show only those rows where the column B entry is a number, but hide rows where that row holds text or a number, use a criteria range with a blank for the header and the formula =ISNUMBER(B2) for the criteria.

Attach a workbook if needed.