Need customized Sumif formula

H

harinsh

Active Member
Joined
Feb 7, 2012
Messages
273
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

[TABLE="width: 294"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]EMP ID[/TD]
[TD]Amount[/TD]
[TD]Actual Sumif[/TD]
[TD]Require Output[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD="align: right"]-950[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD="align: right"]950[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]33333[/TD]
[TD="align: right"]698[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]33333[/TD]
[TD="align: right"]-698[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]698[/TD]
[/TR]
[TR]
[TD]44444[/TD]
[TD="align: right"]650[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]55555[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]44444[/TD]
[TD="align: right"]-650[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]650[/TD]
[/TR]
[TR]
[TD]55555[/TD]
[TD="align: right"]-250[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Can anyone help me on this...
 
Sort by date Sort by votes
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...

[TABLE="width: 640"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]EMP ID[/TD]
[TD]Amount[/TD]
[TD]Require
Results[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD] $(1,950)[/TD]
[TD]High[/TD]
[TD]High because one of the value is more than $1000[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD] $1,950[/TD]
[TD]High[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD] $100[/TD]
[TD]High[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD] $100[/TD]
[TD]High[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]33333[/TD]
[TD] $698[/TD]
[TD]Low[/TD]
[TD]Low because negative value is less than $1000[/TD]
[/TR]
[TR]
[TD]33333[/TD]
[TD] $(698)[/TD]
[TD]Low[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]44444[/TD]
[TD] $1,650[/TD]
[TD]High[/TD]
[TD]High because of positive value is more than $1000[/TD]
[/TR]
[TR]
[TD]44444[/TD]
[TD] $(650)[/TD]
[TD]High[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]55555[/TD]
[TD] $1,200[/TD]
[TD]High[/TD]
[TD]High because of positive & Negative value is more than $1000[/TD]
[/TR]
[TR]
[TD]55555[/TD]
[TD] $(1,200)[/TD]
[TD]High[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]55555[/TD]
[TD] $(1,200)[/TD]
[TD]High[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]



Thank you very much always!!!
 
Upvote 0

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
See if this does what you want.

Excel Workbook
ABC
1EMP IDAmountRequire Output
211111($1,950)High
311111$1,950High
411111$100High
511111$100High
633333$698Low
733333($698)Low
844444$1,650High
944444($650)High
1055555$1,200High
1155555($1,200)High
1255555($1,200)High
High Low
 
Upvote 0
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..

[TABLE="width: 308"]
<colgroup><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD]Serno[/TD]
[TD]Order[/TD]
[TD]Require Output[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]11111[/TD]
[TD="align: right"]($1,950)[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]11111[/TD]
[TD="align: right"]$1,950[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]11111[/TD]
[TD="align: right"]$100[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]11111[/TD]
[TD="align: right"]$100[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]33333[/TD]
[TD="align: right"]$698[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]33333[/TD]
[TD="align: right"]($698)[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]44444[/TD]
[TD="align: right"]$1,650[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]44444[/TD]
[TD="align: right"]($650)[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]55555[/TD]
[TD="align: right"]$20[/TD]
[TD]Low[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]55555[/TD]
[TD="align: right"]($200)[/TD]
[TD]Low[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]55555[/TD]
[TD="align: right"]($200)[/TD]
[TD]Low[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]66666[/TD]
[TD="align: right"]$10[/TD]
[TD]Low[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]66666[/TD]
[TD="align: right"]$200[/TD]
[TD]Low[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]66666[/TD]
[TD="align: right"]$100[/TD]
[TD]Low[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]99999[/TD]
[TD="align: right"]($800)[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]99999[/TD]
[TD="align: right"]($800)[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]99999[/TD]
[TD="align: right"]$20[/TD]
[TD]High[/TD]
[/TR]
</tbody>[/TABLE]

If you look at above example EMP ID 66666 not showing correctly. Kindly help
 
Upvote 0
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. :confused:
 
Last edited:
Upvote 0
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...
 
Upvote 0
harinsh said:
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...
Click to expand...
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
ABC
211111($1,950)High
311111$1,950High
411111$100High
511111$100High
633333$698High
733333($698)High
844444$1,650High
944444($650)High
1055555$20High
1155555($200)High
1255555($200)High
1366666$10High
1466666$200High
1566666$100High
1699999($800)High
1799999($800)High
1899999$20High
High Low (2)
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
SUMIF for increases in row of data
Replies
5
Views
348
gopalgriffith
G
S
  • Solved
SumIf problem
Replies
2
Views
350
sharshra
S
E
  • Question Question
Need formula to sum data using multiple parameters
Replies
7
Views
642
excel?
E
M
  • Solved
Help/guidance needed on nested Sumif formula condition range
Replies
3
Views
557
Fluff
Fluff
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top