Need customized Sumif formula

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

EMP IDAmountActual SumifRequire Output
11111-950100100
1111195000
3333369800
33333-6980698
4444465000
5555525000
44444-6500650
55555-2500250
1111110000

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

Can anyone help me on this...
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
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?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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