15,968,013 members
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.

```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
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

## Solution 2

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.

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.

## Solution 1

Assuming the above data is in cells A1:D13, add the following formula in cell D14:
VBScript
`=SUMIF(A2:A13,"=100+30", D2:D13)`

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.