My sheet has about 500 people. I need a formula to sum up each person's values
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
sum
0
Answers
0
Selected Answer
Try this formula.
=SUMIFS($C:$C,$A:$A,$A2,$B:$B,$B2)It will draw the total from column C where the code in column A = A2 and the name in column B = B2. You can place the formula in any cell in row 2 and copy it down to other rows.
Discussion
Using entire columns will slow the sheet down with the SUMIFS function.
Better use B1: B1000 or sthg like that
Better use B1: B1000 or sthg like that
pecoflyer Aug 27, '17 at 11:10 am
@pecoflyer A string like B1:B1000 has its own drawbacks. It breaks all rules for designing formulas for easy maintenance in the hope that maintenance will never be required. Maintenance is always required and should therefore be always prepared for. The formula I suggested truly needs no maintenance. But if speed is a concern the better way would be to use a named range. The advantage of a named range is that you can modify it in one place instead of modifying thousands of formulas. (If there aren't thousands of formulas speed will not be an issue :-))
Variatus (rep: 4889) Aug 27, '17 at 10:47 pm
Glad to see you talking again! Honestly though, discussions like this I think really can benefit users who come here seeking answers since, as they say, "there's more than one way to skin a cat." ;)
don (rep: 1989) Aug 29, '17 at 1:27 pm
@Don The forum enables multiple answers for that purpose. Suggesting improvements to existing answers is just plain bad manners. @pecoflyer should have posted his own answer and explain its advantages. That would have been constructive.
Variatus (rep: 4889) Aug 29, '17 at 9:23 pm
I understand that. But, to be honest, this is why I wanted to have discussion sections as well as answer sections, so that people can comment on an Answer, add their two cents, and not disrupt the flow of actually answering a question. I know this can be annoying when someone posts a comment/discussion instead of an Answer sometimes, but, for this example at least, I personally found your discussion, spurred by the comment from pecoflyer, to be helpful and I think future readers will too.
don (rep: 1989) Aug 31, '17 at 7:42 am
It worked. Thanks a million.
Jalal (rep: 2) Sep 4, '17 at 1:26 pm
You are very welcome.
Variatus (rep: 4889) Sep 4, '17 at 9:11 pm
Don, what do you mean by " Glad to see you two talking again!" ? Should I know this person?
pecoflyer Dec 1, '17 at 11:37 am
Honestly, I'm not sure at this point haha. My brain is a bit scrambled from everything I've been doing for the site in the past few months and this was a while back.
don (rep: 1989) Dec 1, '17 at 11:43 am
This kind of pontification makes me think of someone of the days of XLKey
pecoflyer Dec 2, '17 at 12:24 pm
For the most part, we try to keep the Answers on topic and so they only contain information that answers the question and then the discussion section, where these comments are, is a bit more conversational. The idea though, is to keep the forum as much a Q&A forum as possible though, and I think it's working better than a traditional forum setup so far.
don (rep: 1989) Dec 4, '17 at 5:28 am