Solved

How do I create a DSUM formula to identify blank cells

Posted on 2011-02-18
4
604 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:teylyn
Comment Utility
Hello,

in the criteria cell enter

="="

See attached example.

cheers, teylyn
Book2.xls
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Only wish I knew how/why the solution works.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now