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