The Computer Oracle

How can I "group by" and sum a column in excel?

--------------------------------------------------
Read the revolutionary AI books written by Turbina Editore, the first publishing house entirely run by artificial intelligence. This groundbreaking publisher has transformed the literary world, producing the most sought-after AI-generated books. Start reading today at:
https://turbinaeditore.com/en/
--------------------------------------------------

--------------------------------------------------
Hire the world's top talent on demand or became one of them at Toptal: https://topt.al/25cXVn
and get $2,000 discount on your first invoice
--------------------------------------------------

Take control of your privacy with Proton's trusted, Swiss-based, secure services.
Choose what you need and safeguard your digital life:
Mail: https://go.getproton.me/SH1CU
VPN: https://go.getproton.me/SH1DI
Password Manager: https://go.getproton.me/SH1DJ
Drive: https://go.getproton.me/SH1CT


Music by Eric Matyas
https://www.soundimage.org
Track title: The World Wide Mind

--

Chapters
00:00 How Can I &Quot;Group By&Quot; And Sum A Column In Excel?
00:40 Accepted Answer Score 100
00:52 Answer 2 Score 92
01:20 Answer 3 Score 31
01:57 Answer 4 Score 11
02:51 Thank you

--

Full question
https://superuser.com/questions/405555/h...

--

Content licensed under CC BY-SA
https://meta.stackexchange.com/help/lice...

--

Tags
#microsoftexcel

#avk47


ACCEPTED ANSWER

Score 100


The feature you want to use is Pivot Tables. It's really easy to do exactly what you want with just a few clicks once you learn how to use that feature.



ANSWER 2

Score 92


You can also do this with the Subtotal feature.

  1. Click the Data tab in Excel's ribbon toolbar
  2. Click the Sort button and sort by your category column
  3. Click the Subtotal button and fill in the dialog as appropriate, then click OK

For example...

At each change in: Category

Use function: Sum

Add subtotal to: Cost of Goods Sold



ANSWER 3

Score 31


I am assuming that you are looking for the total COGS for an account like grocery for instance for week 1.

sumif is the easiest way to come up with that data. example taken from your picture:

=sumif(d2:d?,"grocery",j2:j?)

I have placed the question marks since I cannot see the whole sheet. You would select the entire column D where you have account data. D2 thru D?, Same with weekly usage column J2 thru J?.

The formula will look in the D column for the text grocery, if it is present it will add the cells in column J that correspond with D columns that have the word grocery in it. (or whatever text you put in the formula for it to look for)



ANSWER 4

Score 11


The most trivial approach may be a Excel Sort operation followed by a Excel Subtotal operation.

It yields what you seek without changing the whole layout of your data as Pivot operation would.

Sorting

  • Click the Data tab (or press Alt-A on keyboard)
  • Click Sort (or press S on keyboard)
  • Click the drop down arrow next to "Sort by" in the main area of popup window.
  • Select "Category" since this is what you want to group by.
  • Click OK!

Subtotal

  • Click the Data tab (or press Alt-A on keyboard)
  • Click Subtotal icon (or press B on keyboard)
  • Now that your data is sorted, read and fill the Subtotal popup like you were reading natural English: 
           At each change in
          Category (Click dropdown arrow and select this)
       Use function
          Sum (Click dropdown arrow and select this)
       Add subtotal to
          Cost of Good Sold (Select the columns you wanted to total for individual categories)
    


       Click OK!