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.]

Okay, the database range is pink. The criteria range is blue. And, a 2-input table is yellow.

See the Excel Help topic for "Create a two-variable data table".

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Status	Type		Status	Type
2	AAA	xyz
3	BBB	abc
4	CCC	jkl
5	AAA	xyz		14	xyz
6	BBB	jkl		AAA	3
7	CCC	abc		BBB	0
8	AAA	xyz		CCC	1
9	BBB	def
10	CCC	jkl
11	AAA	abc
12	BBB	rst
13	CCC	rst
14	AAA	def
15	CCC	xyz
16
Sheet1

 

[Mark W.]

Okay, the database range is pink. The criteria range is blue. And, a 2-input table is yellow.

See the Excel Help topic for "Create a two-variable data table".

On the far right is a PivotTable that can be created to summarize the database.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Status	Type		Status	Type		CountofStatus	Type
2	AAA	xyz					Status	xyz
3	BBB	abc					AAA	3
4	CCC	jkl					BBB
5	AAA	xyz		14	xyz		CCC	1
6	BBB	jkl		AAA	3		GrandTotal	4
7	CCC	abc		BBB	0
8	AAA	xyz		CCC	1
9	BBB	def
10	CCC	jkl
11	AAA	abc
12	BBB	rst
13	CCC	rst
14	AAA	def
15	CCC	xyz
Sheet1

 

[indiantrix]

I know there's lots of critics here for array entered formulae, but it works for me, so here is one solution: Once the formula is typed (without the {} braces) into the top/left cell of the array, press Control+Shift+ENTER. This will add the braces, and then you can copy the formula across and down as far as needed.
Hope this gets you closer to a solution.
/s/ Larry

array.xls
	A	B	C	D	E	F	G	H	I
1	Status	Type			xyz	abc	jkl	def	rst
2	AAA	xyz		AAA	3	1	0	1	0
3	BBB	abc		BBB	0	1	1	1	1
4	CCC	jkl		CCC	1	1	2	0	1
5	AAA	xyz
6	BBB	jkl
7	CCC	abc
8	AAA	xyz
9	BBB	def
10	CCC	jkl
11	AAA	abc
12	BBB	rst
13	CCC	rst
14	AAA	def
15	ccc	xyz
Sheet1

 

[indiantrix]

Excuse the post...I got to the bottom of page one in the string and failed to notice that there was a page 2! Mark, I like your work better. /s/ Larry