Help with AND function

Question

corinne1972 Author

Level 1

8 points

Help with AND function

For the life of me, I can't figure out what I'm doing wrong. I have a "Transactions" table with all of my financial transactions with date, account and reconciled checkbox columns. I have a second "Reconciliation" table to indicate whether or not I have marked all of my transactions as reconciled for specific months with the following formula:

AND(Account="Triangle-Corinne", Transactions::Transactions::"A>=C2, Transactions::Transactions::A<=D2, Transactions::Transactions::Reconciled=True).

It works on the first row, but when I fill down the column it doesn't work. I have triple-checked the dates and that all of the transactions between them are checked, but it still does not return true for subsequent rows. I also tried adding the formula manually in the second row with no luck. Help!

Posted on Feb 20, 2019 11:07 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,720 points

Feb 20, 2019 6:44 PM in response to corinne1972

Your AND formula does not work the way you are thinking. "A>=C2" does not compare every row of column A to the value in C2, it compares only one cell to the value in C2. If the formula is in row 2, it is equivalent to "A2>=C2". I think you might be able to use COUNTIFS to do what you want. In that formula, "A" will refer to the whole column. You want to "count if" A >=C2, A <=D2 and Reconciled = FALSE. If the result is zero, all items are reconciled for that set of dates.

=COUNTIFS(Transactions:::Transactions::A, ">="&C2, Transactions:::Transactions::A, "<="&D2, Transactions:::Transactions::Reconciled, FALSE)=0

Reply

Answer 2

Feb 20, 2019 3:18 PM in response to corinne1972

Please select the cell A2, then double click the cell to enter the formula editor, then select the formula and paste here. If possible, post a screenshot of the table with the transactions. Mask or remove the personal data and leave dates and checkboxes.

Reply

Answer 3

Badunit

Level 7

30,720 points

Feb 20, 2019 6:29 PM in response to corinne1972

EDIT- deleted

Reply

Answer 4

corinne1972 Author

Level 1

8 points

Feb 21, 2019 9:10 AM in response to Badunit

Took me awhile, but I finally got it to work. Thanks!!!!

Reply

Answer 5

Badunit

Level 7

30,720 points

Feb 21, 2019 3:34 PM in response to corinne1972

Glad you got it. Usually I recreate the document/tables and try out the proposed formula/solution but I didn't this time. I hope it was close enough if not totally correct.

Reply

Answer 6

corinne1972 Author

Level 1

8 points

Feb 20, 2019 3:28 PM in response to Wayne Contello

I don't have a formula in A2, column A is just text. The formula is in B2:

Copied and pasted:

AND(Account="Triangle-Corinne",Transactions::Transactions::A≥C2,Transactions::Transactions::A≤D2,Transactions::Transactions::Reconciled=TRUE)

I'm not able to post the whole table as there are too many transactions, but here is a sample with the relevant columns (some are hidden).

Reply