# Matching debit and credit (accounting)

1. ## Matching debit and credit (accounting)

Hi and thanks for reading and helping me.

What I want: A formula that matches debit/credit amounts that sums to 0 on the same account.

For example:

account 1500 with amount +100 should be matched with account 1500 amount -100, but not with another +100.

I have almost solved this problem by reading other threads (thank you very much), but I still have an issue: Similar amounts in credit or debit also get interpreted as "TRUE" when they should not.

For example:

account 1500 with amount +110 should be matched with account 1500 amount -110, but not with another +110. Similarly double negative(credit) amounts should not be matched.

I think the problem is related to the formula using the absolute value, hence not being able to distinguish mathematical signs.

The formula I am using:

=SUMPRODUCT((\$A\$2:\$A\$999999=\$A2)*(ABS(\$B\$2:\$B\$999999)=ABS(\$B2)))>1

Thank you.

2. ## Re: Matching debit and credit (accounting)

Try this: =SUMPRODUCT((\$A\$2:\$A\$16=A2)*(\$B\$2:\$B\$16=-B2))>=1

3. ## Re: Matching debit and credit (accounting)

The problem was partially solved, and a new one arose:

The function does not work if there are more than one amount to match with, see attachment.

I would like the formula to just pick one of them so the SUM of all true amounts should be 0.

Side note: There are millions of accountants in the world who could be help with a formula like this - a bit weird that one doesn't exist (?).

4. ## Re: Matching debit and credit (accounting)

Try this formula:
=IF(COUNTIFS(\$B\$2:B2,B2,\$A\$2:A2,A2)>1,FALSE,IF(COUNTIFS(\$B:\$B,-B2,\$A:\$A,A2)>0,TRUE,FALSE))

5. ## Re: Matching debit and credit (accounting)

Is something wrong with that formula? I can't paste it inn without getting an error. Im trying to paste it in C2.

6. ## Re: Matching debit and credit (accounting)

There is nothing wrong with the formula. I used in your sample. What error are you getting?
I've attached the file as well.

7. ## Re: Matching debit and credit (accounting)

Ok, some progress. Thank you.

But if i ad more transactions that should be matched which are similar, the function does not work, see attachment.

Any solution to this?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1