Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How do I create a DSUM formula to identify blank cells

Posted on 2011-02-18
4
Medium Priority
?
663 Views
Last Modified: 2013-11-05
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?
0
Comment
Question by:T4Pam
  • 2
  • 2
4 Comments
 
LVL 50
ID: 34930971
Hello,

in the criteria cell enter

="="

See attached example.

cheers, teylyn
Book2.xls
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 34931129
Actually, it will be sufficient to just enter a

=

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

cheers, teylyn
0
 

Author Comment

by:T4Pam
ID: 34937647
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
 

Author Closing Comment

by:T4Pam
ID: 34937651
Only wish I knew how/why the solution works.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question