How do I create a DSUM formula to identify blank cells
Posted on 2011-02-18
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?