Page 1 of 1

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

Posted: 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.

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

Posted: 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.

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

Posted: 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)