sum by item no and number

[yky]

I need to count not only by item no but also color. In the following example, the desired result is in blue. Row 5 and 6 have the same item number and color. Thus, the sum is 22 (16+6). The closest thing I can get is with the following formula, whose result is in the last column. It seems I need a filter to filter off 22 and 31 in red. How?

=SUMIFS($D$2:$D$168,$B$2:$B$168,"="&B2,$C$2:$C$168,"="&C2)



[TABLE="width: 250"]
<tbody>[TR]
[TD]89219 MOD
[/TD]
[TD]WHITE[/TD]
[TD]16[/TD]
[TD]16[/TD]
[TD]16
[/TD]
[/TR]
[TR]
[TD]89228 MOD[/TD]
[TD]GOLD[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]89228 MOD[/TD]
[TD]PLATINUM[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]89228 MOD[/TD]
[TD]WHITE[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]89320 MOD[/TD]
[TD]BLACK[/TD]
[TD]16[/TD]
[TD]0[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]89320 MOD[/TD]
[TD]BLACK[/TD]
[TD]6[/TD]
[TD]22[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]89320 MOD[/TD]
[TD]CREAM[/TD]
[TD]16[/TD]
[TD]0[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]89320 MOD[/TD]
[TD]CREAM[/TD]
[TD]15[/TD]
[TD]31
[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]89320 MOD[/TD]
[TD]WHITE[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]89320 MOD[/TD]
[TD][/TD]
[TD]17[/TD]
[TD]17[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]89330 MOD[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[etaf]

=SUMIFS($D$2:$D$168,$B$2:$B$168,"="&B2,$C$2:$C$168,"="&C2)

i cant see from the example blue

the colour is in column b
the item is that in column C or A

b2 and c2 - does that have the colour and item criteria you want to sum

an example

lets assume
B1 has "BLUE"
A1 has item id "89330mod"


=SUMIFS($D$2:$D$168,$B$2:$B$168,B1,$CA$2:$A$168,A1)

=SUMIFS(range to sum , range with colour, colour criteria blue , range of id , id criteria )
so
if you change B1 to red
it will now sum red

 

[yky]

Thanks for the reply.

Too many colors. The font color of column d is blue. That is where the desired results reside, 16, 1, 6, 6, 0, 22, ...

 

[etaf]

ok, so you want to sum based on font colour not the word colour
that will probably need VBA , not my expertise

although some examples of what answers you expect and why may help others

 

[Aladin Akyurek]

In D1 enter and copy down:

=IF(COUNTIFS($A$1:A1,A1,$B$1:B1,IF(B1="","",B1))=COUNTIFS($A$1:$A$11,A1,$B$1:$B$11,IF(B1="","",B1)),SUMIFS($C$1:$C$11,$A$1:$A$11,A1,$B$1:$B$11,IF(B1="","",B1)),0)

 

[Peter_SSs]

If your sample data is representative in that it is sorted on column A and then on column B, or it could be sorted that way, then this copied down should suffice.

Book1
	A	B	C	D
1
2	89219 MOD	WHITE	16	16
3	89228 MOD	GOLD	1	1
4	89228 MOD	PLATINUM	6	6
5	89228 MOD	WHITE	6	6
6	89320 MOD	BLACK	16	0
7	89320 MOD	BLACK	6	22
8	89320 MOD	CREAM	16	0
9	89320 MOD	CREAM	15	31
10	89320 MOD	WHITE	10	10
11	89320 MOD		17	17
12	89330 MOD		1	1
Sum By Item & Colour

Cell Formulas
Range	Formula
D2	=IF(AND(A2=A3,B2=B3,B2<>""),0,SUM(C$2:C2)-SUM(D$1:D1))