compare 2 columns based on their positive and negative values

[pooja2010]

Dear Excel experts,

I want to compare 2 columns based on their positive and negative values..

For example,

A B C
ID1 91 -7
ID2 -7 74
ID3 -4 -5
ID4 90 4
ID5 -55 34
ID6 33 3

I want to know 1) which id's have both negative values in both columns B & C, 2) which id's have both positive values in both columns B & C, and which id's have opposite signs in both columns B & C.

I want to have a sum of each one of that.. Can anyone help me with that?

Your help appreciated. Thanks in advance.

 

[jeffreybrown]

How about...

=LOOKUP(SIGN(B2)+SIGN(C2),{-2,0,2},{"Both Negative","One of each","Both Positive"})

 

[circledchicken]

Hi,

With your data like this:

Excel 2013
	A	B	C	D	E	F	G	H	I
1	ID1	91	-7	Different signs		Different signs	Both -	Both +	Both 0
2	ID2	-7	74	Different signs		3	1	2	0
3	ID3	-4	-5	Both -
4	ID4	90	4	Both +
5	ID5	-55	34	Different signs
6	ID6	33	3	Both +
Sheet1

Try this in D1 and drag down:

=IF(
    SIGN(B1) = SIGN(C1),
    LOOKUP(SIGN(B1), {-1,0,1}, "Both " & {"-",0,"+"}),
    "Different signs")

And for the sum of each one, this in F2 and drag across:

=COUNTIF($D1:$D6, F1)

Adjust your ranges as required.

 

[pooja2010]

Hey thanks a lot for such a nice demonstration.. It works for me

 

[markmzz]

If I understand correctly what you want, maybe this can help you:

Layout:

[TABLE="width: 240"]
<tbody>[TR]
[TD="class: xl63, width: 27, bgcolor: transparent"]A
[/TD]
[TD="class: xl63, width: 27, bgcolor: transparent"]B
[/TD]
[TD="class: xl63, width: 27, bgcolor: transparent"]C
[/TD]
[TD="class: xl63, width: 27, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 27, bgcolor: transparent"]+
[/TD]
[TD="class: xl63, width: 27, bgcolor: transparent"]-
[/TD]
[TD="class: xl63, width: 27, bgcolor: transparent"]+/-
[/TD]
[TD="class: xl63, width: 27, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 27, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 27, bgcolor: transparent"]+
[/TD]
[TD="class: xl63, width: 27, bgcolor: transparent"]-
[/TD]
[TD="class: xl63, width: 27, bgcolor: transparent"]+/-
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]ID1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]-7
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]ID4
[/TD]
[TD="class: xl64, bgcolor: yellow"]ID3
[/TD]
[TD="class: xl64, bgcolor: yellow"]ID1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Sum
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]130
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]-9
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]130
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]ID2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]-7
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]74
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]ID6
[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"]ID2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]ID3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]-4
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]-5
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"]ID5
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]ID4
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]90
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]ID5
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]-55
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]34
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]ID6
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]33
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[/TR]
</tbody>[/TABLE]


Formulas:

E2-> =IFERROR(INDEX($A$2:$A$7,MATCH(0,INDEX(COUNTIF(E$1:E1,$A$2:$A$7)+(($B$2:$B$7<0)+($C$2:$C$7<0)),),0)),"")

F2-> =IFERROR(INDEX($A$2:$A$7,MATCH(0,INDEX(COUNTIF(F$1:F1,$A$2:$A$7)+(($B$2:$B$7>=0)+($C$2:$C$7>=0)),),0)),"")

G2-> =IFERROR(INDEX($A$2:$A$7,MATCH(0,INDEX(COUNTIF(G$1:G1,$A$2:$A$7)+(($B$2:$B$7>=0)*($C$2:$C$7>=0)+ ($B$2:$B$7<0)*($C$2:$C$7<0)),),0)),"")

J2-> =SUMPRODUCT(SUMIF($A$2:$A$7,E$2:E$6,$B$2:$B$7)+SUMIF($A$2:$A$7,E$2:E$6,$C$2:$C$7))

Markmzz

 

[pooja2010]

Yes thanks a lot.. Its of great help..