[Solved] Sum cells in a column based on info in another

Discuss the spreadsheet application
Post Reply
Burnart
Posts: 8
Joined: Thu Apr 05, 2018 12:37 am

[Solved] Sum cells in a column based on info in another

Post by Burnart »

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.
Last edited by Hagar Delest on Thu Jun 10, 2021 7:50 am, edited 1 time in total.
Reason: tagged solved.
OpenOffice 4.1.1 on Windows Vista
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: sum cells in one column based on info in another column

Post by robleyd »

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.
Attachments
simple_pivot.ods
(9.25 KiB) Downloaded 105 times
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: sum cells in one column based on info in another column

Post by robleyd »

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.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Matareuz
Posts: 23
Joined: Fri Nov 20, 2020 4:33 pm
Location: Venezuela

Re: Sum cells in one column based on info in another column

Post by Matareuz »

Hey,

You also can use the "SUMIF" function to do the same.

Code: Select all

=SUMIF(A2:A15;"Alpha";B2:B15)
Or instead of put "Alpha" as a string you could put it as a reference.

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 :oops:
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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Sum cells in one column based on info in another column

Post by FJCC »

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.
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Sum cells in one column based on info in another column

Post by MrProgrammer »

Matareuz wrote:Or you also can use the "SUMPRODUCT" function, nevertheless as far as I know, the function "SUMPRODUCT" isn't avaible in OpenOffice.
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.

[Tutorial] The SUMPRODUCT function
Burnart wrote:I want a routine that will Total all the Alpha, Beta, Gamma and Delta numbers …
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.
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).
Burnart
Posts: 8
Joined: Thu Apr 05, 2018 12:37 am

Re: Sum cells in one column based on info in another column

Post by Burnart »

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.
OpenOffice 4.1.1 on Windows Vista
Post Reply