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

Question

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'm trying to achieve this using Google spreadsheets.

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.

Answer 1

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.

Answer 2

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!