AND

In your journal entry testing you want to select the manual postings from the memorial journal (MEM) with an amount of at least 10,00010.000, because those are the entries you inspect first. You want a column that says TRUE or FALSE per journal line.

Formula in D2

=AND(A2="MEM", C2>=10000)

=AND(A2="MEM"; C2>=10000)

Result: TRUE

Watch out: If the export writes the journal as "MEM " with a trailing space, A2="MEM" is FALSE for every manual entry and nothing is flagged. Clean the column first with TRIM, or compare TRIM(A2)="MEM".

You sent balance confirmations to customers. The sheet lists the difference per relation between the receivables ledger and the confirmed balance, and a column where you tick TRUE once the confirmation has come back.

A relation is cleared when the confirmation is received and the difference is at most the follow-up threshold of 750 in G1. Some confirmations have not arrived yet, so the cell is still empty, and those must stay open.

Formula in D2

=IF(AND(C2=TRUE, B2<=$G$1), "Cleared", "Open")

=IF(AND(C2=TRUE; B2<=$G$1); "Cleared"; "Open")

Result: Open

Watch out: If someone types the word ja in column C instead of TRUE, C2=TRUE is FALSE and the relation stays Open forever. Use data validation so the column only holds TRUE or FALSE.

=IF(AND(C2=TRUE, B2<=$G$1), 0, B2)

=IF(AND(C2=TRUE; B2<=$G$1); 0; B2)

For journal entry testing you want a live list of entry numbers from the journal in F1 with an amount of at least the performance materiality in F2. The list must update when either input changes.

FILTER needs one TRUE or FALSE per row, which is exactly what AND cannot deliver, so the two conditions are multiplied.

Formula in F3

=FILTER(A2:A8, (B2:B8=F1) * (C2:C8>=F2))

=FILTER(A2:A8; (B2:B8=F1) * (C2:C8>=F2))

Result: 25-0501 / 25-0504 / 25-050625-0501 / 25-0504 / 25-0506

Watch out: Writing FILTER(A2:A8, AND(B2:B8=F1, C2:C8>=F2)) looks natural, but AND squeezes all fourteen tests into one FALSE. FILTER then gets one value for seven rows and returns #VALUE!.

=SUM((B2:B8=F1) * (C2:C8>=F2))

=SUM((B2:B8=F1) * (C2:C8>=F2))

=COUNTIFS(B2:B8, F1, C2:C8, ">=" & F2)

=COUNTIFS(B2:B8; F1; C2:C8; ">=" & F2)

Before you rely on an XAF auditfile export, every journal line must pass three completeness checks: period at least 1, period at most 12, and a ledger account filled in. You want one formula that returns TRUE or FALSE for every line and grows with the export, readable enough for a colleague to add a fourth check later.

Formula in D2

=BYROW(A2:C7, LAMBDA(rec,
  LET(
    period, INDEX(rec, 2),
    account, INDEX(rec, 3),
    AND(period >= 1, period <= 12, account <> "")
  )
))

=BYROW(A2:C7; LAMBDA(rec;
  LET(
    period; INDEX(rec; 2);
    account; INDEX(rec; 3);
    AND(period >= 1; period <= 12; account <> "")
  )
))

Result: TRUE / FALSE / FALSE / FALSE / TRUE / FALSETRUE / FALSE / FALSE / FALSE / TRUE / FALSE

Watch out: The mirror-image mistake is AND(B2:B7 >= 1, B2:B7 <= 12, C2:C7 <> "") without BYROW. It returns one FALSE in D2, which looks like a result for the first line, while it really says that not every line is valid.

=(B2:B7 >= 1) * (B2:B7 <= 12) * (C2:C7 <> "") = 1

=(B2:B7 >= 1) * (B2:B7 <= 12) * (C2:C7 <> "") = 1