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

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

 

[Joe4]

I am looking at your data example, and I think you need to explain your logic, because it does not seem to make sense to me, mainly:
- How is it determined which lines the totals should appear on? In your example, it looks like every row with a negative value in the Amount column. But what if there are two negative amounts for the same EMP ID? Then which line would you want the SUM on?
- The amounts in your Require Output column don't make sense to me. I can see if you sum up all the records for EMP ID 11111, you get 1000, but the summed total for 33333 would be 0, so I am not sure why you are showing 698. Same thing for 44444 and 55555. I get 0 if I sum up all their records.

 

[harinsh]

Hi Joe,

Thanks for your reply.

Its my bad not explaining the logic properly, what I am trying to get here is when I do the sumif based on EMP ID will get the sum of amount for that particular emp ID. but I need to get total sum results only at once not twice why because this out data is help for another report input data.

If you look at the table ...the out I given only once..when we simple sum if the result will get it all the rows whereever emp ID same.

For example : if EMP ID 55555 has two lines one negative of 250 and positive of 250...in this case I should only get the out in one line not two lines, output should be positive of 250.

I hope I explained it properly now.

 

[Joe4]

I hope I explained it properly now.

Unfortunately not. I understand you only want to see one line per EMP ID, but I am failing to see how you arrive at your numbers. Except for EMP ID 11111, none of the them appear to be "sums".

For example : if EMP ID 55555 has two lines one negative of 250 and positive of 250...in this case I should only get the out in one line not two lines, output should be positive of 250.

If there is one negative of 250 and one positive of 250, shouldn't the sum be 0?
If not, then you are actually not talking about a sum, but rather something else.
I fail to see the logic at work here.

 

[harinsh]

Hi Joe,

Let me explain you what exactly need with different example....

Assume I have Emp IDs in column A which is repetitive in nature against that in column B I have values these values contains both negative and positive values. I have one condition if anything greater than $1000 per EMP ID I need to tag as "High" or else "Low". The condition should like irrespective of negative or positive the threshold applies same.

For example: If EmpID 55555 has amount in two lines i.e one line $1200 and another line has ($1200) negative when I do sumif I will get "zero" value because same value has both negative and positive in two lines. If I want to get the "High" and "Low" condition simple if condition will not get it. I need something else need to add to that formula which I am looking for it.

Please let me know if not clear will give table with another example....Thank you very much

 

[Joe4]

So it is still not clear exactly what it is you are after, and how to arrive there.

Going back to your original example, I assume that the "Require Output" column is what you want, is it not?
Are the amounts you posted there correct, in relation to the sample data you have shown?

If so, please go through and explain for each and every instance (for 11111, 22222, 33333, 44444, 55555) and explain:
- why you picked it to show it on the line you showed it
- how exactly you arrive at that particular amount

 

[harinsh]

Hi Jeo,

Please ignore my original example...and refer the latest query I posted....

Hi Joe,

Let me explain you what exactly need with different example....

Assume I have Emp IDs in column A which is repetitive in nature against that in column B I have values these values contains both negative and positive values. I have one condition if anything greater than $1000 per EMP ID I need to tag as "High" or else "Low". The condition should like irrespective of negative or positive the threshold applies same.

For example: If EmpID 55555 has amount in two lines i.e one line $1200 and another line has ($1200) negative when I do sumif I will get "zero" value because same value has both negative and positive in two lines. If I want to get the "High" and "Low" condition simple if condition will not get it. I need something else need to add to that formula which I am looking for it.

Please let me know if not clear will give table with another example....Thank you very much

 

[Joe4]

I am a very visual person. Would you mind posting a new data sample, like you did in your first post?
A lot of times, seeing the data the way you want it along with the explanation helps to clarify things.

 

[harinsh]

My apologies for delay reply...here I given the requirement based on the criteria

EMP ID Amount Require Output
11111 $(1,950) High
11111 $1,950 High
33333 $698 Low
33333 $(698) Low
44444 $1,650 High
55555 $1,200 High
44444 $(650) Low
55555 $(1,200) High
11111 $100 Low

Assume I have Emp IDs in column A which is repetitive in nature against that in column B I have values these values contains both negative and positive values. I have one condition if anything greater than $1000 per EMP ID I need to tag as "High" or else "Low". The condition should like irrespective of negative or positive the threshold applies same.

For example: If EmpID 55555 has amount in two lines i.e one line $1200 and another line has ($1200) negative when I do sumif I will get "zero" value because same value has both negative and positive in two lines. If I want to get the "High" and "Low" condition simple if condition will not get it. I need something else need to add to that formula which I am looking for it.

 

[Peter_SSs]

if anything greater than $1000 per EMP ID I need to tag as "High"

In that case ..

- Why is the last result "Low" when EMP ID 11111 has 1950 near the top of the table?

- Why is the second 44444 row "Low" when 4444 also has a value of 1650?