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

[cid]

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.

[FJCC]

You can use a formula like

Code: Select all

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.

[cid]

Code: Select all

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)