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?
Thanks in advance.
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
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
then drag filled that to the right (AK2) and the count of 94 remained.
perhaps that will solve your problem. If not, contact me with more information.
---------- 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.
Thanks in advance
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.