Formual: Find the Cell Criteria

Macpop

New Member
Joined
Aug 20, 2014
Messages
14
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:

CompanyDebit
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.
 

Some videos you may like

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,)

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
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","")

Adjust the ranges as needed
 

Macpop

New Member
Joined
Aug 20, 2014
Messages
14
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","")

Adjust the ranges as needed

Awesome! this works... Thank you :)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,201
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,768
Messages
5,524,784
Members
409,600
Latest member
Dunnowhatfor

This Week's Hot Topics

Top