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
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
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
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
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)
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
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.
