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$10
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?
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$10
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
And thank you. I will award full points.
ASKER
Only wish I knew how/why the solution works.
in the criteria cell enter
="="
See attached example.
cheers, teylyn
Book2.xls