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

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

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

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
FJCC
Moderator

Posts: 7259
Joined: Sat Nov 08, 2008 8:08 pm

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

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

### Who is online

Users browsing this forum: Google [Bot], mountain-artisian and 28 guests