Solved

How do I create a DSUM formula to identify blank cells

Posted on 2011-02-18
4
607 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

Expert Comment

by:Ingeborg Hawighorst
ID: 34930971
Hello,

in the criteria cell enter

="="

See attached example.

cheers, teylyn
Book2.xls
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Multiple Open Excel Spreadsheets 12 40
how to add loop into this VBA 3 28
How best to represent data in Excel? 13 25
Excel VBA - Constants 4 15
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

770 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