HELP! sumifs or sumproduct... arrays?

[goforlexi]

I have a seemingly simple problem, but can't figure out the excel behind it... here's a simplified mock up of the data involved:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Letters[/TD]
[TD]Colors[/TD]
[TD]Numbers[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Blue[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Red[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Blue[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Pink[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Purple[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Blue[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Pink[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Pink[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Yellow[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Blue[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Red[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

I need to find a way to get 6, which is the sum of "numbers" where blue is in A, B, and C, but does not duplicate of where blue is in A twice.

This needs to be done on a larger data set that has approximately 200 different letters and about 30 colors.

Thanks in advance for any help.

 

[sophea_tum]

I have a seemingly simple problem, but can't figure out the excel behind it... here's a simplified mock up of the data involved:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Letters[/TD]
[TD]Colors[/TD]
[TD]Numbers[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Blue[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Red[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Blue[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Pink[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Purple[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Blue[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Pink[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Pink[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Yellow[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Blue[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Red[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

I need to find a way to get 6, which is the sum of "numbers" where blue is in A, B, and C, but does not duplicate of where blue is in A twice.

This needs to be done on a larger data set that has approximately 200 different letters and about 30 colors.

Thanks in advance for any help.

Hi,

you can remove the duplicated data first then do the normal sumif.

let say that "Letters" is located in cell A1 then you can selected from A1:C12 then go to Data=> Remove duplicated

after remove you can do sumif formula.

let me know if this is not the way of what you want.
thanks

 

[Aladin Akyurek]

[...]

I need to find a way to get 6, which is the sum of "numbers" where blue is in A, B, and C, but does not duplicate of where blue is in A twice.

This needs to be done on a larger data set that has approximately 200 different letters and about 30 colors.
...

Note that a result of 6 (for any letter and blue) is only possible if letter and color duplicates are always associated with the same number.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]Letters[/td][td]Colors[/td][td]Numbers[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]A[/td][td]Blue[/td][td]

1​

[/td][td][/td][td]Blue[/td][td]

6​

[/td][/tr]
[tr][td]

3​

[/td][td]A[/td][td]Red[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]A[/td][td]Blue[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]A[/td][td]Pink[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]A[/td][td]Purple[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]B[/td][td]Blue[/td][td]

2​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]B[/td][td]Pink[/td][td]

2​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]B[/td][td]Pink[/td][td]

2​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]B[/td][td]Yellow[/td][td]

2​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]C[/td][td]Blue[/td][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]C[/td][td]Red[/td][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

F2, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$12<>"",IF($B$2:$B$12=$E2,
   MATCH($A$2:$A$12,$A$2:$A$12,0))),ROW($A$2:$A$12)-ROW($A$2)+1),
   $C$2:$C$12))

Copy down this formula for other colors column E might house.