Need customized Sumif formula

[harinsh]

Hi Excel Experts,

I need to customise the Sumif formula here...if you look at the below table..typical sumif will give the require numbers however, I should get the sumif results at once not at every line where we have same lines....for example below

EMP ID	Amount	Actual Sumif	Require Output
11111	-950	100	100
11111	950	0	0
33333	698	0	0
33333	-698	0	698
44444	650	0	0
55555	250	0	0
44444	-650	0	650
55555	-250	0	250
11111	100	0	0

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

Can anyone help me on this...

 

[harinsh]

Hi Peter, Yes your right and it's my bad given you wrong example...here you can see the updated table ...please help me with formula to achieve this logic...

EMP ID	Amount	Require Results	Comment
11111	$(1,950)	High	High because one of the value is more than $1000
11111	$1,950	High
11111	$100	High
11111	$100	High
33333	$698	Low	Low because negative value is less than $1000
33333	$(698)	Low
44444	$1,650	High	High because of positive value is more than $1000
44444	$(650)	High
55555	$1,200	High	High because of positive & Negative value is more than $1000
55555	$(1,200)	High
55555	$(1,200)	High

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Thank you very much always!!!

 

[Peter_SSs]

See if this does what you want.

Excel Workbook
	A	B	C
1	EMP ID	Amount	Require Output
2	11111	($1,950)	High
3	11111	$1,950	High
4	11111	$100	High
5	11111	$100	High
6	33333	$698	Low
7	33333	($698)	Low
8	44444	$1,650	High
9	44444	($650)	High
10	55555	$1,200	High
11	55555	($1,200)	High
12	55555	($1,200)	High
High Low

 

[harinsh]

Hi Peter,

Sorry for the delay reply. I was out of station so, could not able to reply to your solution.

It's working for the $1000 and when I change the value to $250 ..its not working do you know any reason? Do I need to Tweak the formula other than 1000 number change..

Serno	Order	Require Output	Result
2	11111	($1,950)	High
3	11111	$1,950	High
4	11111	$100	High
5	11111	$100	High
6	33333	$698	High
7	33333	($698)	High
8	44444	$1,650	High
9	44444	($650)	High
10	55555	$20	Low
11	55555	($200)	Low
12	55555	($200)	Low
10	66666	$10	Low
11	66666	$200	Low
12	66666	$100	Low
10	99999	($800)	High
11	99999	($800)	High
12	99999	$20	High

<colgroup><col span="2"><col span="2"></colgroup><tbody>
</tbody>

If you look at above example EMP ID 66666 not showing correctly. Kindly help

 

[Peter_SSs]

Why should 66666 be "High" as none of the values are above 250?

Is it because the sum of the positive numbers is > 250? If so, I misinterpreted your original question and perhaps was misled by your earlier sample data where there were no examples similar to this.

 

[harinsh]

Hi Peter,

Its my bad if again confused with my example earlier....its not only greater than 250 in any of the value and even it should also consider Sum value of that particular EMP ID...in the above example EMP ID 66666 should be high because if you sum the value for this EMP ID ..its coming more than 250 value.

Two Conditions....(i) If any one value more than 250 irrespective of positive or negative (i) if sum value of emp ID more than 250 in all these two cased should we "high" if anything below its should be "low"

Hope it clarifies now...

 

[Peter_SSs]

Two Conditions....(i) If any one value more than 250 irrespective of positive or negative (i) if sum value of emp ID more than 250 in all these two cased should we "high" if anything below its should be "low"

Hope it clarifies now...

I'm not sure if it has clarified. The red text indicates to me that in your previous sample, 55555 should also be high since -200 -200 is -400 & greater than 250 but you did not mention that one.

Anyway, based on what you have written, it seems like this to me.

Excel Workbook
	A	B	C
2	11111	($1,950)	High
3	11111	$1,950	High
4	11111	$100	High
5	11111	$100	High
6	33333	$698	High
7	33333	($698)	High
8	44444	$1,650	High
9	44444	($650)	High
10	55555	$20	High
11	55555	($200)	High
12	55555	($200)	High
13	66666	$10	High
14	66666	$200	High
15	66666	$100	High
16	99999	($800)	High
17	99999	($800)	High
18	99999	$20	High
High Low (2)

 

[harinsh]

I think this is working fine ..Thanks for your help...greatly appreciate..