Array or SumIF Formula

[ultracyclist]

I have my data as shown in rows G7 thru M31. I want to create a formula on each row in column M that takes the value in the quantity columns and the text values in the adjacent rows and gives a sum total.

Ex: Yellow= 270, Blue = 2,430, Red =7500, Black 15360, and Green = 870

Here’s two examples

If I enter 2 in the Qty column (G3) and Yellow in the color column (H3) it should return a value of 540 on row M3

Another example

If I enter 3 in a Qty column (G6) and Blue in the color column (H6) then enter 4 in another Qty column (K6) and Black in the color column (L6) the value should return a value of 68,730 on row M6

Any suggestions on how to create this formula?

<style type="text/css">
.tg  {border-collapse:collapse;border-spacing:0;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
.tg .tg-yw4l{vertical-align:top}
</style>
[TABLE="class: tg"]
<tbody>[TR]
[TH="class: tg-yw4l"]Qty
[/TH]
[TH="class: tg-yw4l"]Color
[/TH]
[TH="class: tg-yw4l"]Qty
[/TH]
[TH="class: tg-yw4l"]Color
[/TH]
[TH="class: tg-yw4l"]Qty
[/TH]
[TH="class: tg-yw4l"]Color
[/TH]
[TH="class: tg-yw4l"]Total
[/TH]
[/TR]
[TR]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[/TR]
[TR]
[TD="class: tg-yw4l"]2
[/TD]
[TD="class: tg-yw4l"]Yellow
[/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"]540.00
[/TD]
[/TR]
[TR]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[/TR]
[TR]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[/TR]
[TR]
[TD="class: tg-yw4l"]3
[/TD]
[TD="class: tg-yw4l"]Blue
[/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"]4
[/TD]
[TD="class: tg-yw4l"]Black
[/TD]
[TD="class: tg-yw4l"]68,730.00
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks,

Allen

 

[jarjarbingie]

I have my data as shown in rows G7 thru M31. I want to create a formula on each row in column M that takes the value in the quantity columns and the text values in the adjacent rows and gives a sum total.

Ex: Yellow= 270, Blue = 2,430, Red =7500, Black 15360, and Green = 870

Here’s two examples

If I enter 2 in the Qty column (G3) and Yellow in the color column (H3) it should return a value of 540 on row M3

Another example

If I enter 3 in a Qty column (G6) and Blue in the color column (H6) then enter 4 in another Qty column (K6) and Black in the color column (L6) the value should return a value of 68,730 on row M6

Any suggestions on how to create this formula?

<style type="text/css">
.tg  {border-collapse:collapse;border-spacing:0;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
.tg .tg-yw4l{vertical-align:top}
</style>
[TABLE="class: tg"]
<tbody>[TR]
[TH="class: tg-yw4l"]Qty[/TH]
[TH="class: tg-yw4l"]Color[/TH]
[TH="class: tg-yw4l"]Qty[/TH]
[TH="class: tg-yw4l"]Color[/TH]
[TH="class: tg-yw4l"]Qty[/TH]
[TH="class: tg-yw4l"]Color[/TH]
[TH="class: tg-yw4l"]Total[/TH]
[/TR]
[TR]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[/TR]
[TR]
[TD="class: tg-yw4l"]2[/TD]
[TD="class: tg-yw4l"]Yellow[/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"]540.00[/TD]
[/TR]
[TR]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[/TR]
[TR]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[/TR]
[TR]
[TD="class: tg-yw4l"]3[/TD]
[TD="class: tg-yw4l"]Blue[/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"][/TD]
[TD="class: tg-yw4l"]4[/TD]
[TD="class: tg-yw4l"]Black[/TD]
[TD="class: tg-yw4l"]68,730.00[/TD]
[/TR]
</tbody>[/TABLE]

Thanks,

Allen

there is probably a better a solution than this but here's what I can think of at the moment:

create a helper table with this formula:

say the original table is in columns G:L

M8 = IF(M$7="Qty",G8,IF(G8="Yellow",270,IF(G8="Blue",2430,IF(G8="Red",7500,IF(G8="Black",15360,IF(G8="Green",870,0)))))) drag up to column R
S8 = SUM(IF($M$7:$R$7="Qty",$M8:$Q8*$N8:$R8,0)) press CTRL+SHIFT+ENTER

 

[ultracyclist]

Thanks for the suggestion. I tried this but its not going to work in this situation since I have data in other columns after Column M.

 

[Marcelo Branco]

Try something like this

Create a table Color - Value (A1:B6)

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

1​

Color​

Value​

Qty​

Color​

Qty​

Color​

Qty​

Color​

Total​

2​

Yellow​

270​

3​

Blue​

2430​

2​

Yellow​

540​

4​

Red​

7500​

5​

Black​

15360​

6​

Green​

870​

3​

Blue​

4​

Black​

68730​

Formula in M2 copied down
=IF(AND(COUNT(G2:K2),COUNTA(H2:L2)),SUMPRODUCT(SUMIF($A$2:$A$6,H2:L2,$B$2:$B$6)*N(+G2:K2)),"")

Hope this helps

M.

 

[ultracyclist]

Hi Marcelo,

Thank you for your help. That worked perfect.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.