Cannot find CountIfs combination to recognize a blank cell

[HMSTPI]

I have a list of rules in text form that perform various integrity check on data in a table. Here are just 2 of the currently 13 rules in the list (along with the hidden field that turn the field name into valid table cell refs):

The relevant columns/fields in the Trxns table for a record giving me fits are:

The check formula, in the [Edit_Code] column, uses INDIRECT to execute the rules on each row of table and return the text applicable on each row that fails a test. Assuming the above tests are on rows 2 & 3 the formula is:

=IFS(COUNTIFS(INDIRECT($AV$2),$N$2,INDIRECT($AX$2),$P$2&$Q$2,INDIRECT($AZ$2),$S$2)=1,$K$2,​

COUNTIFS(INDIRECT($AV$3),$N$3,INDIRECT($AX$3),$P$3&$Q$3,INDIRECT($AZ$3),$S$3)=1,$K$3,​

TRUE,"ok")​

I have narrowed it down to the highlighted condition as being the problem -- but no matter how it is set, it keeps resulting in "ok" when it should result in "Disp'd RJCD Sec ID". I have tried:

SecIDOvrd < "0"​

SecIDOvrd = 0​

SecIDOvrd = ""​

What am I missing? I think I have always made do by restructuring the formula to countif length of the field = 0 -- but in a table of 13 integrity checks -- all of which are expected to work in the same manner to make it easy to add and remove checks as things change.... I don't have the flexibility here to do that. Something OUGHT to WORK here!

Thanks for any help!

 

[jorismoerings]

Hi,

The error is within concatenating P$3&$Q$3. The combination will not evaluate to the criterium you want to use (ic is the cell blank) because you've forgot to take the text identifier of the cell into account.
See this example where Range (A1:A2) holds 2 true blanks, Range (B1:B2) holds 2 created blanks by formula:

Book1
	A	B	C	D	E	F	G	H
1				0	0		=	""
2				1	1
Sheet1

Cell Formulas
Range	Formula
D1:E1	D1	=COUNTIFS(A1,$G$1&$H$1)
D2:E2	D2	=COUNTIFS(A2,$G$2&$H$2)
B1:B2	B1	=IF(A1=0,"",0)

If you evaluate the formula in E1 you'll see Excel has added the text identifiers.

How to solve, just delete P3 and Q3 or in my example G2 and H2 so they will be blank then countifs will treat the blank cells as criteria, like this

From a lack of more info i'm unsure if this solution will cause problems somewhere else in your sheet.

Hope this helps.

 

[HMSTPI]

Well -- I would have NEVER have thought that just leaving both the qualifier and the criteria blank would work, but it certainly did! Thanks for the quick reply!

 

[jorismoerings]

No problem - glad i could help - thanks for the feedback.