Sum Range of Values Related to Serial Number only if text does not appear in an adjacent column

[doublecaesar]

I'm currently working with a data set that includes Serial Numbers with a range of values to sum, but only if a 3rd column does not contain a specific text. Column D is the formula column that I am trying to figure out how to achieve.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Serial Number[/TD]
[TD]Distance[/TD]
[TD]Color[/TD]
[TD]Total Distance if All Blue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1001[/TD]
[TD]10[/TD]
[TD]Blue[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1001[/TD]
[TD]10[/TD]
[TD]Blue[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1001[/TD]
[TD]10[/TD]
[TD]Blue[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1002[/TD]
[TD]10[/TD]
[TD]Yellow[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1002[/TD]
[TD]10[/TD]
[TD]Blue[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1002[/TD]
[TD]10[/TD]
[TD]Blue[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1003[/TD]
[TD]10[/TD]
[TD]Yellow[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]1003[/TD]
[TD]10[/TD]
[TD]Yellow[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1003[/TD]
[TD]10[/TD]
[TD]Yellow[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
I've been attempting Sumifs, however I keep coming back with a sum of all the B rows for each serial number that aren't yellow (1002 would be 20, 1003 would be zero).

 

[Fluff]

Hi & welcome to the board.
How about

Excel 2013 32 bit
	A	B	C	D
1	Serial Number	Distance	Color	Total Distance if All Blue
2	1001	10	Blue	30
3	1001	10	Blue	30
4	1001	10	Blue	30
5	1002	10	Yellow	20
6	1002	10	Blue	20
7	1002	10	Blue	20
8	1003	10	Yellow	0
9	1003	10	Yellow	0
10	1003	10	Yellow	0
Members

Cell Formulas
Range	Formula
D2	=SUMIFS(B$2:B$10,A$2:A$10,A2,C$2:C$10,"<>Yellow")

 

[doublecaesar]

Hi & welcome to the board.
How about

Excel 2013 32 bit

	A	B	C	D
Serial Number	Distance	Color	Total Distance if All Blue
Blue
Blue
Blue
Yellow
Blue
Blue
Yellow
Yellow
Yellow

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]30[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]30[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]30[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"] 20 [/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"] 20 [/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"] 20 [/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1003[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1003[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1003[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]0[/TD]

</tbody>

Members​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]D2[/TH]
[TD="align: left"]=SUMIFS([COLOR=rgb(255]B$2:B$10,A$2:A$10,A2,C$2:C$10,"<>Yellow"[/COLOR])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for the quick response, that is basically where I had gotten. However I am needing the serial numbers in rows 5,6,7 to total as 0 because all the text values in the range are not Blue. Basically if any text besides Blue is in any of the rows that have the same serial number, I want it to not be summed and return as zero.

 

[doublecaesar]

So I've created a workaround - added a column for IF formula to get just Yellow Distance, and Blue Distance and then hidden them. Now my sumifs will pull zero.