Click here to Skip to main content
15,942,093 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
Hi,
I wanted to calculate sum of ACCOUNT_VALUE based on IDENTIFIER column for each row. Sum up the values for each common identifier and gets populated in the new column. basically for 100+30 identifier sum up value should be $8000.
Please help me on this.

IDENTIFIER	ACCOUNT_ID	MODEL_ID	ACCOUNT_VALUE
100+30          100        30       $1,000 
105+35          105        35       $1,500 
110+40          110        40       $500 
100+30          100        30       $2,000 
105+35          105        35       $2,500 
110+40          110        40       $1,000 
100+30          100        30       $3,500 
105+35          105        35       $1,800 
110+40          110        40       $700 
100+30          100        30       $1,500 
105+35          105        35       $2,000 
110+40          110        40       $2,500


What I have tried:

I tried SUMIF function but unfortunately, it is not giving desired output
Posted
Updated 1-May-24 5:19am
v2
Comments
Richard MacCutchan 1-May-24 11:01am    
Please show the code you used and explain why the answer is not correct.
0x01AA 1-May-24 14:51pm    
Pivot

Assuming the above data is in cells A1:D13, add the following formula in cell D14:
VBScript
=SUMIF(A2:A13,"=100+30", D2:D13)
 
Share this answer
 
Comments
Member 14124697 1-May-24 11:31am    
this formula wouldn't work since I have bunch of data. around 20000 records and many identifiers so i can't every time change formula for =100+30.

Any other solution?
Richard MacCutchan 1-May-24 11:56am    
Then you will need to modify the formula to accept different criteria, or create a macro that allows you to enter the data requirements.
RedDk 1-May-24 13:54pm    
The solution is exactly as RMC states; as you hint though, note that with 20000 records, a better placement for the formula cell would be somewhere south of D2:D20001. Say ... D2:D20114.
You are using the wrong technique to solve this. Excel provides a built-in capability that would do this for you in a matter of seconds. What you want to do is create a pivot table. The steps to accomplish this are:
  1. Select the entire range you want to use to perform the calculation
  2. Under the Insert ribbon tab, select Pivot Table. This lets you create the pivot table from your range. For convenience sake, choose New Worksheet to create the pivot table based on your data in a clean working area.
  3. You have a blank pivot table. Drag the Identifier field down into the Rows section.
  4. Drag the ACCOUNT_VALUE field down into the Values section and ensure it is set to Sum of ACCOUNT_VALUE. If you want to change the name that is displayed, you can use the Value Field Settings to change it to something more meaningful.
Congratulations, you now have a sheet that summarizes the identifiers and gives the total value for them.
 
Share this answer
 
Comments
Richard MacCutchan 3-May-24 8:19am    
+5. I had a feeling there was something better, but haven't done a lot of Excel recently.
Pete O'Hanlon 3-May-24 8:23am    
Thanks Richard. I was playing around with combinations of VLOOKUP and SUMIF's then realised this could be done with a lot less difficulty.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900