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.