How to sumup values based identifier

Question

1.00/5 (3 votes)

See more:

Excel-2016

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 1-May-24 4:19am

Member 14124697

Updated 1-May-24 5:19am

Dave Kreskowiak

v2

Add a Solution

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

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard MacCutchan · Answer 1 · 2024-05-01T05:09:00

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)

Posted 1-May-24 5:09am

Richard MacCutchan

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.

Pete O'Hanlon · Answer 2 · 2024-05-02T20:06:00

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:

Select the entire range you want to use to perform the calculation
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.
You have a blank pivot table. Drag the Identifier field down into the Rows section.
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.