Solved

How do I create a DSUM formula to identify blank cells

Posted on 2011-02-18
4
623 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

751 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