Hello OO Users as you can tell I'm not very good at spreadsheets because I can't even figure out how to title this succinctly. Essentially my problem is this I have 2 columns of information one is a list of
'criteria' and the other is a number associated with each criteria entry. Something like this:
Criteria Number
Beta 12
Gamma 0
Delta 3
Alpha 2
Alpha 7
Gamma 33
Beta 0
Gamma 2
Gamma 0
Delta 0
Alpha 3
Beta 1
Delta 10
Gamma 0
I want a routine that will Total all the Alpha, Beta, Gamma and Delta numbers and give me a result that looks like this;
Alpha 12
Beta 13
Gamma 35
Delta 13
I'm sure this is standard spreadsheet stuff but I can't figure it out. I haven't even had any luck trying to figure out what words to use to search for a previous answer to this question - my searches turned up blanks. Any help would be greatly appreciated.
[Solved] Sum cells in a column based on info in another
[Solved] Sum cells in a column based on info in another
Last edited by Hagar Delest on Thu Jun 10, 2021 7:50 am, edited 1 time in total.
Reason: tagged solved.
Reason: tagged solved.
OpenOffice 4.1.1 on Windows Vista
Re: sum cells in one column based on info in another column
You could use a pivot table - see the Help - F1 - for Pivot Table. Also there is Using the DataPilot - now called Pivot Table - in the AOO wiki.
See the attached example using your data above.
See the attached example using your data above.
- Attachments
-
- simple_pivot.ods
- (9.25 KiB) Downloaded 110 times
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: sum cells in one column based on info in another column
BTW if you are new to spreadsheets you may find [Tutorial] Ten concepts that very Calc user should know helpful.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Sum cells in one column based on info in another column
Hey,
You also can use the "SUMIF" function to do the same.
Or instead of put "Alpha" as a string you could put it as a reference.
Or you also can use the "SUMPRODUCT" function.
You also can use the "SUMIF" function to do the same.
Code: Select all
=SUMIF(A2:A15;"Alpha";B2:B15)
Or you also can use the "SUMPRODUCT" function.
Code: Select all
=SUMPRODUCT(A2:A15="Alpha";B2:B15)
Edit: Ops SUMPRODUCT is avaible in OpenOffice, I did know Thank you FJCC and MrProgrammer for tell me |
Last edited by Matareuz on Fri Jun 11, 2021 9:58 pm, edited 2 times in total.
LibreOffice 6.3.2.2 Windows 7 Ultimate
Re: Sum cells in one column based on info in another column
SUMPRODUCT is available in OpenOffice.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Sum cells in one column based on info in another column
I used SUMPRODUCT with OpenOffice in 2010 before LibreOffice existed. It is a very old function and was probably available in the OpenOffice predecessor, StarOffice, with version 3 in 1995, a quarter century ago. SUMPRODUCT has many advantages over SUMIF and is one of the most important, versatile functions in a spreadsheet.Matareuz wrote:Or you also can use the "SUMPRODUCT" function, nevertheless as far as I know, the function "SUMPRODUCT" isn't avaible in OpenOffice.
[Tutorial] The SUMPRODUCT function
For the OP's task, a Pivot Table (formerly called Data Pilot) is the easiest solution. The desired result can be created in about 20 seconds, and no formulas are needed. Any book in your local library about spreadsheets written in the last 20 years will discuss pivot tables. The concepts are the same, no matter what software you use.Burnart wrote:I want a routine that will Total all the Alpha, Beta, Gamma and Delta numbers …
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Sum cells in one column based on info in another column
Thanks to all those who responded. I'll have a look at each suggestion and see what I can get to work! Before I came to the forum I was looking at some information about SUMPRODUCT and couldn't get it to work but I'll definitely have another go.
I'll let you know when I have success.....
Success! PROBLEM SOLVED. I've had a bit of a play - the SUMPRODUCT or SUMIF approaches I think would be useful when only dealing with a couple of criteria but in this instance I have over 100 criteria and thousands of entries so it's definitely a Pivot Table solution. Looking at the info that robelyd directed me to I realised I have done this before about a decade ago - shows you how little I have to use spreadsheets.
I'll let you know when I have success.....
Success! PROBLEM SOLVED. I've had a bit of a play - the SUMPRODUCT or SUMIF approaches I think would be useful when only dealing with a couple of criteria but in this instance I have over 100 criteria and thousands of entries so it's definitely a Pivot Table solution. Looking at the info that robelyd directed me to I realised I have done this before about a decade ago - shows you how little I have to use spreadsheets.
OpenOffice 4.1.1 on Windows Vista