98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Aggregating Numbers in Excel
Aggregating Numbers in Excel
Resolved · Urgent Priority · Version 2016
Fri 1 Dec 2017, 10:46Delegate Vincent said...
Vincent has attended:
Excel Intermediate course
Excel Advanced course
Excel Advanced - Formulas & Functions course
Aggregating Numbers in Excel
to simplify, I have 3 different criteria (lets say apples, oranges and bananas) in column A, and different quantities for each in column B. How do I sum up the quantities so that I will get just 3 total number? I deally, I would this shown in column c at the bottom of the criteria
In reality, I have over 100 criteria, so I don't want to do a simple autosum on every single criteria
Fri 1 Dec 2017, 10:56Trainer Jens said...
RE: Aggregating Numbers in Excel
Hi Vincent,
Thank you for the forum question.
If I understand you right you can use the Sumif function.
=Sumif(B2:B2000, "Banana")
=Sumif(B2:B2000, "Oranges")
=Sumif(B2:B2000, "Apples")
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 1 Dec 2017, 11:03Delegate Vincent said...
RE: Aggregating Numbers in Excel
Thanks Jens, the only issue is that I have a lot more than 3 criteria, and it can change on a daily basis.
could I do something like a vlookup?
the criteria is in actual fact a list of accounts so there are about 30
Fri 1 Dec 2017, 11:09Trainer Jens said...
RE: Aggregating Numbers in Excel
Hi Vincent,
If you sort column A ascending you can use the SubTotal on the data tab in the Outline group.
Or what about a PivotTable?
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 1 Dec 2017, 11:32Delegate Vincent said...
RE: Aggregating Numbers in Excel
Subtotal works great, thanks!
One final thing, do you know if there is a way to automatically make the subtotal numbers in bold? Currently only the criteri substotal is in bold (i.e. "Apples Total" appears in bold only, and not the number)
Fri 1 Dec 2017, 11:45Trainer Jens said...
RE: Aggregating Numbers in Excel
Hi Vincent,
You can use Conditional Formatting.
How to do it depend on how many columns you have, but if you have a look at the link below, it is very well explained.
http://www.ozgrid.com/Excel/excel-bold-subtotals.htm
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 6 Dec 2017, 10:46Trainer Wendy said...
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Quickly select a block of dataTo quickly select a block of data make sure your active cell is somewhere whithin the block of data and then press Ctrl+* or Ctrl+Shift+8. |
