Why is my formula getting an error?

[tn312c]

Hi, I have a database in SheetA and the first row is the title line. One of the columns is called STATUS and another is called TYPE. The formula I am getting an error is in another sheet.

The formula is attempting to count the number of records in the database, whose STATUS matches the value in A13 and whose TYPE is "xyz." Then the sum of the data in C1 thru C12 is subtracted from the result.

My formula is:
=DCOUNT(SheetA!$B$1:SheetA!$L$2000,STATUS,(MID(STATUS,FIND($A13,STATUS,1),LEN($A13))=$A13)=AND(LEFT(STATUS,LEN($A13))=STATUS)+AND(TYPE<>"xyz"))-SUM($C1:$C12)

My problem, I think, has to do with not knowing when to:
- Prefix function with "=" and when not to. I might have used the "=" properly in the above formula, but...
- Use the double quotes for column name in functions. My mind keeps telling me that by using the quotes, I am teling EXCEL that it is a string "STATUS", rather than indicating the column in the database, where EXCEL is to extarct the status string.

Hope you can help me with this problem too. Thank you in advance...

 

[Mark W.]

As indicated in the Excel Help topic for "DCOUNT worksheet function"...

Criteria is the<font color=red>range of cells</font> that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying a condition for the column.

Examples for such criteria ranges can be found in the Excel Help topic for "Examples of advanced filter criteria".

And, yes, the DCOUNT's field argument should be quoted if used...

Field indicates which column is used in the function.<font color=red>Field can be given as text with the column label enclosed between double quotation marks, such as "Age" or "Yield"</font>, or as a number that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.

...however, the DCOUNT's field argument is optional, and generally, is omitted.
This message was edited by Mark W. on 2002-11-05 20:25

 

[tn312c]

Thank you!!!

 

[tn312c]

To go to "Examples of advanced filter criteria", am I to use Microsoft EXCEL's Help or, is the help in MREXCEL's EXCEL Help. If it is the latter, then I am having a hard time finding where to click to enter the help pages. Please help with the instruction. Sorry, for being such a novice user. Again, thank you...

 

[Mark W.]

Sorry for the confusion... Keep in mind that I'm a BIG advocate of mastering Excel's online help system... there's a lot of useful (free) information there that's often overlooked.

Launch Excel. Press F1 or choose the Help | Microsoft Excel Help menu command. Enter or paste "Examples of advanced filter criteria" (without the quotes) in the input field, and press [ Search ]. This exact topic will appear in a bulleted, hypertext list that you can click on for the help topic text. Of course, I already knew that this topic existed. If you're doing a search from scratch just enter a meaningful keyword (e.g., criteria) and see what appears.
This message was edited by Mark W. on 2002-11-06 10:14

 

[tn312c]

I am really confused!

If I read correctly, I must create a filter table of columns and rows for each a formula I plan on writing. Did I interpret accurately?

My scenario is as follows:
Except for the top row that has my column headings and the first column that has the text string I want to match (see table below), all the other cells have formulas.
TABLE1 below has a sample results shown in each cell.

AAA BBB CCC
xyz 1 3 0
abc 4
def 1

As mentioned in my first append, each formula is applied against a "database" in SheetA. That database has many more columns than STATUS and TYPE. But, for my example, those 2 are the ones my formula is using. And, I am looking for the count of records, where STATUS matches the cell's column heading (e.g., $A13) and TYPE that matches the string specified, namely "xyz."

Hence, the formula in each cell is different from that in another cell. Also, I have many many tables, like TABLE1.

Given that, I am concern about having to create or define a table for each formula.

I think my understanding is flawed, because the implementation cannot be that complicated for something as simple as what I want to do.

Please help me through my fogginess. Thank you in advance...

P.S. I don't know if what I am asking for is allowable. But, if it is, then please send me an email to indicate if calling me at my home phone is an option.

 

[tn312c]

Hi Mark. Your post and mine crossed. Yes, I finally found out where to look in EXCEL's Help. But, my understanding of what I read didn't leave me with a good feeling. Hopefully, I interpreted what I ned to do is incorrect.

 

[tn312c]

Hope I didn't wear out my welcome I really need some help!

 

[Mark W.]

I'm struggling a bit understanding both the nature of your data list and your criteria range. Here's a stab at it. Notice how the DCOUNT function highlighted in yellow references both the data list and criteria cell ranges. Keeping in mind that multiple conditions represented by the contents of a criteria range are AND-ed if the occupy multiple columns and OR-ed if the occupy multiple rows...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	yourdatalist:				criteriarange:
2
3	STATUS	TYPE			STATUS	TYPE
4	A	xyz			A	xyz
5	B	123
6	C	abc
7	A	xyz			2
8	C	abc
9	A	123
10
11
12
13	A
14
Sheet1

 

[tn312c]

Sorry, for not being clear. Also, I noticed that my example table got jumbled up.

Following should be better, I hope and please ignore my previous descriptions, as I do not know how to return to the last post from this email, without losing what I've typed so far.

My database is shown next. (I could not duplicate your copy of your examples, when I tried to copy my tables from Excel.) In case the tables shown below get jumbled up again, I'll describe them.

My database has 2 columns. The STATUS column has records with values "AAA", "BBB" and "CCC". The TYPE column has records with "xyz", "abc" and "jkl", etc. My database has the following records:

Status Type
AAA xyz
BBB abc
CCC jkl
AAA xyz
BBB jkl
CCC abc
AAA xyz
BBB def
CCC jkl
AAA abc
BBB rst
CCC rst
AAA def
CCC xyz

I have a table in a different sheet and I'll call this other sheet, SheetB. The database and the table must reside in 2 different sheets.

I had called the table in SheetB, TABLE1. In this table I am looking for the count of records for each intersection of STATUS and TYPE.

STATUS-> AAA BBB CCC
TYPE -> xyz 3 0 1
def 0 1 0
So, from my database, there are 3 records that match TYPE=xyz and STATUS=AAA. Zero for xyz and BBB. One for xyz and CCC. Similarly, I have 0,1,0 for def and AAA/BBB/CCC.

The formulas for the cell intersections are similar. The formula for STATUS=AAA and TYPE=xyz is as follows:

=DCOUNT(SheetA!$B$1:SheetA!$L$2000,"Status",(MID("STATUS",FIND($B13,"STATUS",1),LEN($B13))=$B13)=AND(LEFT("STATUS",LEN($B13))=STATUS)=AND("TYPE"<>$A14))

SheetA!$B$1:SheetA!$L$2000 is the maximum range of my database. I gave the maximum database range, because I do not know from one month to the next how many records I'll be having in the database. Hence, it is possible for the formula to encounter a lot of blank or null records.

"STATUS" and "TYPE" are column labels in the database in SheetA.

$B13 is the cell address for "AAA" in TABLE1 in SheetB. And, $A14 is the cell containing "xyz"

The error I am getting is #VALUE!. When I tried to find the error per "Show Calculation Steps", I believe Excel does not like how I specified the FIND function. Perhaps, that is the first error it detected and there may be more after I resolved the FIND problem. But, I don't know what to do to correct the FIND issue.

I have been on this for hours and have tried different ways of specifying the formula, but with no success. Sometimes I get error, #NAME?

What changes must I make to get around the error in my formula?

Hoping to hear from you soon.

Thanks in advance...
This message was edited by tn312c on 2002-11-08 12:21