# Formual: Find the Cell Criteria

#### Macpop

##### New Member
Good Day,

I am trying to decipher a formula that will check in a range of cells to determine if a cell criteria has the identical values in two separate cells.

See example:

 Company Debit Credit BX, INC \$0.00 \$1,500.00 LX, INC \$0.00 \$28,293.68 FX, INC \$0.00 \$23,257.00 BX, INC \$1,500.00 \$0.00 LX,INC \$28,293.68 \$0.00

<tbody>
</tbody>

So in this example the formula should mark company BX,INC and company LX,INC as the companies that have both a credit and debit amount. Where as company LX, INC and FX, INC do not have both a credit and debit amount.

In addition, the formula should be able to identify pairs. So if BX, INC appeared with another 1500 credit balance and no debit balance of 1500, then the formula would not mark that cell.

I think there is a formula for this. Any assistance will be valuable.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

withdrawn

Last edited:

#### Roderick_E

##### Well-known Member
here you go... assuming your data is starting at A1 through column C. Input this into cell D2 and drag down.

Code:
``=IF(SUMIF(\$A\$2:\$A\$6,A2,\$C\$2:\$C\$6)=SUMIF(\$A\$2:\$A\$6,A2,\$B\$2:\$B\$6),"Deb&Cred","")``

#### Macpop

##### New Member
here you go... assuming your data is starting at A1 through column C. Input this into cell D2 and drag down.

Code:
``=IF(SUMIF(\$A\$2:\$A\$6,A2,\$C\$2:\$C\$6)=SUMIF(\$A\$2:\$A\$6,A2,\$B\$2:\$B\$6),"Deb&Cred","")``

Awesome! this works... Thank you

#### Peter_SSs

##### MrExcel MVP, Moderator
In addition, the formula should be able to identify pairs. So if BX, INC appeared with another 1500 credit balance and no debit balance of 1500, then the formula would not mark that cell.
Awesome! this works... Thank you
Does that mean you don't want the two green rows below paired?

Excel Workbook
ABCD
1CompanyDebitCredit
2BX, INC0.001,500.00
3LX, INC0.0028,293.68Deb&Cred
4FX, INC0.0023,257.00
5BX, INC1,500.000.00
6LX, INC28,293.680.00Deb&Cred
7BX, INC0.001,500.00
Matching Values

Replies
82
Views
5K
Replies
0
Views
131
Replies
0
Views
191
Replies
4
Views
282
Replies
0
Views
236