# Excel dont count if and

#### humonculi

##### New Member
I'm doing some homework on excel and I've came across a little problem. I have a list of companies transactions (not a real companies) and there seems to be a little problem with the books. The companies didn't write what was a credit and what was a mistake. How to fin what is a mistake is simple. Credit= negative amount on top so a credit would be
-3,179.00 \$ 120(account number)
41,684.88 \$ 240(account number)
87.50 \$ 320(account number)
3,495.75 \$ 460(account number)
And a mistake would have
3,179.00 \$ 120 (account number)
(-3,179.00 \$) 120 (account number) <------- a correction
A correction under but with the same account and the same amount (not always right after, sometimes further)

(-507.50 \$) 320(account number) <------------- a credit
41,684.88 \$ 240 (account number)

87.50 \$ 320(account number)
3,495.75 \$ 460(account number)
My question is, is there a a formula to calculate
if the amount above is positive is there an equivalent negative amount with the same amount somewhere in the list and it cancels its self out and a counter that restarts

Basically i need a way to have all the negatives (corresponding amount and corresponding account) that come before the debits count as "credit" and have all the other negatives that come after be marked as "mistake"

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
B3: = If (A3+A2=0,"Mistake")

B3: = If (A3+A2=0,"Mistake")
The thing is its not necessarily right above some times its a few rows above

Basically if the same out from the Same account (positive amount)
Is cancels the 1 negative

Hello

ABC
1
AmountCodeNotes
2\$3,179.00120ok
3\$315.00150ok
4\$3,178.00160ok
5\$3,150.00170ok
6\$3,150.00150ok
7\$320.00150ok
8-\$3,150.00150Correction
9\$256.00120ok
10\$564.00120ok
11-\$256.00120Correction
12-\$444.00170Credit
13-\$156.00120Credit
14-\$3,150.00170Correction

<tbody>
</tbody>

Array Formulas
CellFormula
C2
{=IF(A2<0,IF(ISNA(MATCH(0,IF(B\$1:B1=B2,A2+A\$1:A1),0)),"Credit","Correction"),"ok")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

This won't correctly handle an instance where a credit is entered with the same value as a correction previously entered. The formula in C3 can be copied down.

I trust this helps
Andrew

Last edited:
Hello

ABC
1AmountCodeNotes
2\$3,179.00120ok
3\$315.00150ok
4\$3,178.00160ok
5\$3,150.00170ok
6\$3,150.00150ok
7\$320.00150ok
8-\$3,150.00150Correction
9\$256.00120ok
10\$564.00120ok
11-\$256.00120Correction
12-\$444.00170Credit
13-\$156.00120Credit
14-\$3,150.00170Correction

<tbody>
</tbody>

Array Formulas
CellFormula
C2{=IF(A2<0,IF(ISNA(MATCH(0,IF(B\$1:B1=B2,A2+A\$1:A1),0)),"Credit","Correction"),"ok")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

This won't correctly handle an instance where a credit is entered with the same value as a correction previously entered. The formula in C3 can be copied down.

I trust this helps
Andrew

OMFG i didn't think i was possible but you are the best

Replies
4
Views
315
Replies
0
Views
409
Replies
1
Views
925
Replies
7
Views
836
Replies
1
Views
934

1,219,959
Messages
6,151,153
Members
451,011
Latest member
Pigdog89

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back