# Suming values in a column if value in cell next to it matches another cell

I'm trying to create a sheet to maintain a small inventory of servers.

I created a segment where each type of component will be listed at the top.

Below it is a series of copy-pasted chunks which will contain references to components above and their count in that server.

I now want to add another column to the first segment where total count will be listed per component.

Here's an example:

``````     A               B        C
0                           Count
1  Motherboards    MOBO1      ?
2                  MOBO2      ?
3  CPUs            CPU1       ?
4                  CPU2       ?
...
15
16 Server1
17 Motherboard    refToB1     1
18 CPU            refToB4     2
19
...
28 Server2
29 Motherboard    refToB2     1
30 CPU            refToB4     1
``````

I now need to fill cells `C1:C4` so that they show how many of those components are used in total. in this example, that would be `C1=1`, `C2=1`, `C3=0` and `C4=3`.

The formula would be something like this, in human form:

Sum all the cells in `C` column, but only sum the cells if the value in the cell to their left is the same as the value in the cell to the left of the current cell.

I think I could do it all with IFSUM, but I don't know how or can I at all create a condition that is relative to the cell being tested.

This is the solution I came up with after digging around some more:

``````=SUMIF(C:C, INDIRECT("RC[-1]", false), D:D)+SUMIF(G:G, INDIRECT("RC[-1]", false)
``````

This enables me to compare the first range to value left to the cell I'm currently in and if it's a match, analogue value in the second range is added to the sum. Adding two of those together enables me to sum multiple ranges if I want.

``````INDIRECT("R[x]C[y]", false)
``````

This command references the cell which is x rows and c columns away from the cell which contains this command. False is there so relative RC referencing is used instead of absolute A1 referencing.

• The columns in your formula do not correspond to the ones in your question (C, D, G while A, B, C). Could you please fix that. It doesn't really make sense otherwise. If we follow your question we can use the formula `=SUMIF(B\$15:B, INDIRECT("RC[-1]", false), C\$15:C)` in cell `C1` and drag it down to cell `C4`. I think that should be sufficient. – marikamitsos Dec 30 '19 at 1:04

A simpler way to do this would be with new column. Add a new column for the row number (just set to 1..n with a simple formula that sets each value to the one above it plus one). Then, add a new column that is "0" if you don't want to include the values and "1" if you do. So you'd have a new column with something along the lines of `if(C1=D1,1,0)`, if D1 is the simple row number.

Then, all you need to do is a sumproduct of the value you want to add times the new column with 0's or 1's. If that column is a 1, it'll include the value in the sum, if that column is a 0 then it'll wipe it out.

Not as neat as Karlovsky120's answer, but MUCH easier to follow in my opinion!