How do I create a DSUM formula to identify blank cells

I'm trying to get the sum of an array if any one of several variables is true. I've set up a table to define the variables but one of the variable (columns) contains blanks.  I need to get the sum of array where the variable is "blank."

In the formula below, data is contained on worksheet "Expiring" in a workbook.
Worksheet 2 (YTD) contains a table listing the fields (columns on the Data sheet) to be evaluated in a DSUM formula. One of the evaluation columns in Expiring contains some blank cells. I need to come up with the DSUM for those blank cells.

Here is a sample of the formula:  =DSUM(Expiring!$A$1:$AB$10000,"TI",YTD!$W$61:$X$73).
The "W" reference in the above formula, (YTD!W61:X73), is "AE", the name of a column in the Expiring! worksheet, and some of the fields in that column on the Expiring worksheet are blank, i.e., not assigned to any AE.

I need to total dollars in column "TI" for which the AE is blank.

I tried just leaving the AE column in the table (YTD!W61:X73) blank, but that didn't work. I also tried using ="" and ISBLANK but that didn't work either.

If the DSUM function can't be used, is there a way to create a bit of VBA code that would handle the problem?
T4PamAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Actually, it will be sufficient to just enter a

=

in the criteria cell. Just the = sign, nothing else.

cheers, teylyn
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

in the criteria cell enter

="="

See attached example.

cheers, teylyn
Book2.xls
0
 
T4PamAuthor Commented:
Excellent! I have no idea how that works but it does. Is there a resource or way to find out the logic behind that result?


And thank you. I will award full points.  

0
 
T4PamAuthor Commented:
Only wish I knew how/why the solution works.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.