Array or SumIF Formula

U

ultracyclist

Active Member
Joined
Oct 6, 2010
Messages
274
Office Version
  1. 365
Platform
  1. Windows
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?


Code: 
<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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
ultracyclist said:
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?


Code: 
<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
Click to expand...

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
 
Upvote 0
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.
 
Upvote 0
Try something like this

Create a table Color - Value (A1:B6)


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][td="bgcolor: #DCE6F1"]
H​
[/td][td="bgcolor: #DCE6F1"]
I​
[/td][td="bgcolor: #DCE6F1"]
J​
[/td][td="bgcolor: #DCE6F1"]
K​
[/td][td="bgcolor: #DCE6F1"]
L​
[/td][td="bgcolor: #DCE6F1"]
M​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
Color​
[/td][td]
Value​
[/td][td][/td][td][/td][td][/td][td][/td][td]
Qty​
[/td][td]
Color​
[/td][td]
Qty​
[/td][td]
Color​
[/td][td]
Qty​
[/td][td]
Color​
[/td][td]
Total​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
Yellow​
[/td][td]
270​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
Blue​
[/td][td]
2430​
[/td][td][/td][td][/td][td][/td][td][/td][td]
2​
[/td][td]
Yellow​
[/td][td][/td][td][/td][td][/td][td][/td][td]
540​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
Red​
[/td][td]
7500​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
Black​
[/td][td]
15360​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
Green​
[/td][td]
870​
[/td][td][/td][td][/td][td][/td][td][/td][td]
3​
[/td][td]
Blue​
[/td][td][/td][td][/td][td]
4​
[/td][td]
Black​
[/td][td]
68730​
[/td][/tr]
[/table]

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Adding a custom message.
Replies
1
Views
645
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
Collington
  • Question Question
Creating a daily register based on a different worksheet
Replies
3
Views
588
Fluff
Fluff
C
  • Question Question
Transposing like all into the same column
2
Replies
10
Views
1K
Joe4
Joe4
M
  • Question Question
Build HTML Table based on Loop through Excel Worksheet Column / Row condition
Replies
0
Views
943
mystik5
M

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top