Formual: Find the Cell Criteria

[Macpop]

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

<tbody>
[TD="class: xl65, width: 78"] Debit
[/TD]
[TD="class: xl65, width: 105"] Credit [/TD]

[TD="class: xl66"]BX, INC[/TD]
[TD="class: xl67, align: right"]$0.00[/TD]
[TD="class: xl67, align: right"]$1,500.00 [/TD]

[TD="class: xl66"]LX, INC[/TD]
[TD="class: xl67, align: right"]$0.00[/TD]
[TD="class: xl67, align: right"]$28,293.68[/TD]

[TD="class: xl66"]FX, INC[/TD]
[TD="class: xl67, align: right"]$0.00[/TD]
[TD="class: xl67, align: right"]$23,257.00[/TD]

[TD="class: xl66"]BX, INC[/TD]
[TD="class: xl67, align: right"]$1,500.00[/TD]
[TD="class: xl67, align: right"]$0.00 [/TD]

[TD="class: xl66"]LX,INC[/TD]
[TD="class: xl67, align: right"]$28,293.68[/TD]
[TD="class: xl67, align: right"]$0.00 [/TD]

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

 

[Roderick_E]

withdrawn

 

[Roderick_E]

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

=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]

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

=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]

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
	A	B	C	D
1	Company	Debit	Credit
2	BX, INC	0.00	1,500.00
3	LX, INC	0.00	28,293.68	Deb&Cred
4	FX, INC	0.00	23,257.00
5	BX, INC	1,500.00	0.00
6	LX, INC	28,293.68	0.00	Deb&Cred
7	BX, INC	0.00	1,500.00
Matching Values