# Excel/dcount issue

Question
QUESTION: Hi Tom

I have encountered an issue with a dcount formula that I have written.  I have sent you a small workbook to illustrate it.  On Sheet2 cell “AG1” I have written a simple dcount formula that actually works:  =DCOUNT(facultyinformation,1,AJ1:BO2)

The information in cell Sheet 2 AJ2 is fed from Sheet1 cell “D2”.  The formula “=Sheet1!D2” is written in Sheet2 cell “AJ2”.  When I copy the formula across from AJ2 to BO2, I lose the data that should still be in AG1.

Could you shed some light on the possible cause of this?

Chris Mitchell

You have specified the range AJ1:BO2 as your criteria range.

When you put the 9 in D2 of Sheet1 and it appears in AJ2 of Sheet2, then your criteria says to count all the records that have a 9 in the year column.  When you drag fill the =Sheet1!D2 in AJ2 to AK2, it becomes =Sheet1!E2 and returns a zero because Sheet1!E2 is blank.  If I put

Lock, Jamie

in Sheet1!E2, then AG2 of Sheet2 shows Lock, Jamie  and the value in AG1 goes from 94 to 1 because my criteria/filtering condition for the Dcount is

Year=9 and Name="Lock, Jamie"

but using a criteria of Year=9 and Name=0  returns a count of zero since there are no rows that match that criteria.  Again, =Sheet1!E2 returns zero rather than a blank because that is the way links work in excel.  So it appears the solution is to have you formula return a blank when the referenced cell in Sheet1 is blank.

What I did was change your =Sheet1!D2  formula to

=IF(Sheet1!D2="","",Sheet1!D2)

then drag filled that to the right (AK2) and the count of 94 remained.

--
Regards,
Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: Hi Tom

Your solution works.  However the results are slightly skewed because some of the cells in the database are blank.  So, if I apply your solution and place "9" in cell "D2" from sheet 1, the figure returned in "AG1" , from sheet 2 will be 7 and not 94 (as it should be).  This is because there are 7 blank cells in the column marked "groups" in the database.

Could you offer a solution to this issue.

Christopher Mitchell,

No, it is not the 7 blanks in Group that is causing the problems.  Only fill my formula in the first 3 columns and you will see it still returns 94 (this includes the Group column).  If you continue to fill in my formula moving to the right, one column at a time, you will see the count decrease by 1 when you fill it into Aut1.  This is because you have a zero down around row 399.  When I replace that zero with an underscore, the count goes back up to 94.  Keep working to the right and watch the sum.  There is another column where the cells look blank but they have a zero in some of them (looks like you have changed the font color to match the background.  (column labelled: Rank as needed)

I would say you have data problems, but I don't know.  One solution is to only have a criteria range that addresses columns you want to work with.  If you want a generalized criteria column where you can select/choose which columns to use without changing anything else, then you would have to determine what values you have and what values cause problems.  Certainly I would think specific solutions could be constructed for each specific problem - but that probably wouldn't worked for a generalized approach.

I believe I have answered this question and pointed to the source of your problem - I would just apply an autofilter to your filter range, then look at each dropdown and see which ones are showing zeros in the column and correct those or if those values are correct, then you would have to decide what type of filter you want to apply.

--
Regards,
Tom Ogilvy
Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment Thank for this help Tom. I will look at a solution to tightening up my database. regards Chris Mitchell

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

#### Tom Ogilvy

##### Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

##### Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.