[Solved] SUM specific cells based on values in other columns

Discuss the spreadsheet application

[Solved] SUM specific cells based on values in other columns

Postby cid » Sat May 11, 2019 12:57 pm

I'll try to make this as simple as I can, sorry if I'm too blunt:

1. On Sheet1, I have a table of cells (Locations) that I want to automatically update with a value. We can label them Locations 1-5

2. On Sheet2, I have different tables (Spending) where in one column the user can put in a label (Location 1-5), and in the other column, a number (amount spent). This tracks how much was spent in each Location.

3. On Sheet1, I want each cell (Location 1-5) to automatically update with the Sum of all numbers on Sheet2, labelled correspondingly in each table

So for example:

Sheet1:
Locations
1 - $5
2 - $16
3 - $7
4 - $3
5 - $8

Sheet2:
Person 1
1 - $5
3 - $1
3 - $4
2 - $5
5 - $2

Person 2
5 - $6
2 - $7
3 - $2
2 - $4
4 - $3

So any amount that gets labelled with a "1" in the column beside it gets added to all the other amounts labelled "1" and the total is displayed on Sheet1.
So on for 2, 3, 4, 5
Remember that the user can label any amount any number from 1-5.

This could also be done by having a separate table for each Location but that's also way clunkier and takes up way more space.

Please let me know if you need any clarification.
Last edited by cid on Sun May 12, 2019 12:26 am, edited 1 time in total.
OpenOffice 4.1.5 on Window 10
cid
 
Posts: 4
Joined: Sat May 11, 2019 12:42 pm

Re: Adding Cells Together based on the Value of a sister-cel

Postby FJCC » Sat May 11, 2019 1:31 pm

You can use a formula like
Code: Select all   Expand viewCollapse view
SUMIF(Sheet2.A2:A100;1;Sheet2.B2:B100)

to sum all the values in column B that have a 1 in column A.

As an aside, I suggest you change the data layout to have three columns: one each for the person, the location, and the value. It will be easier to handle as the data set grows and you can use a Pivot Table (menu Data -> Pivot Table) to do more complex summaries.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7153
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Adding Cells Together based on the Value of a sister-cel

Postby cid » Sun May 12, 2019 12:24 am

FJCC wrote:
Code: Select all   Expand viewCollapse view
SUMIF(Sheet2.A2:A100;1;Sheet2.B2:B100)


This worked great, thanks!

To summarize for anyone looking here later:

SUMIF(Range to check ; Condition to check for ; Range to add together cells that correspond to cells in the other range that return TRUE)
OpenOffice 4.1.5 on Window 10
cid
 
Posts: 4
Joined: Sat May 11, 2019 12:42 pm


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 41 guests