Multiplying matrix with blanks - Formula help

[Brad B]

I'm not sure what to call the function I'm looking for, not even positive multiplying by a matrix is the correct name for what I want to do, but I'll describe below.

I have a table similar to this:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD](Cell: A1)[/TD]
[TD]Components>[/TD]
[TD]CompA[/TD]
[TD]CompB[/TD]
[TD]CompC[/TD]
[/TR]
[TR]
[TD]Products[/TD]
[TD]Demand[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Prod1[/TD]
[TD]500[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Prod2[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Prod3[/TD]
[TD]2000[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I need to find sum need for each component (Column)
I could write individual formulas in each cell below, ie:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]blank[/TD]
[TD]blank[/TD]
[TD]=c3*b3[/TD]
[TD]=d5*b5[/TD]
[TD]=e3*b3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=e4*b4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]=sum(c1:c4)[/TD]
[TD]=sum(d1:d4)[/TD]
[TD]=sum(e1:e4)[/TD]
[/TR]
</tbody>[/TABLE]

This very tedious manual version will work to get my desired result, but if would much prefer a formula, as the actual table contains over 100 columns and hundreds of rows.

I have seen some formulas involving index and match, and ways to find the nth non-blank value in a column, but it was a little over my head trying to adapt it to my situation in a way that I could quickly copy the formula across and down to get my desired results. (still not ideal to manually change a number in each formula for hundreds of columns/rows...)

Note: I do not necessarily need to know how many components will be needed for each part in the results, just how many total components are needed to meet the need for all products.

Any help would be greatly appreciated!

 

[Max1616]

Hello, if you just want to know the total demand of each comp then you can use this to find out the value:

{=SUM(IF(C2:C5<>"",$B$2:$B$5*C2:C5,0))}

This can then be copied over for each component.

**Keep in mind that this is an array formula. so type this into your cell:
=SUM(IF(C2:C5<>"",$B$2:$B$5*C2:C5,0))
Then double click into the cell and press Control+Shift+Enter and the brackets will then show up around the formula indicating it's an array formula.

 

[Max1616]

Looks like you can also use sum product to accomplish this too (I'm not sure why my mind went immediately to an array formula in the last post haha):

=SUMPRODUCT($B$3:$B$5,C3:C5)

 

[Brad B]

Sumproduct is a much simpler way to find the same result that I'm looking for. I was making it much harder than it had to be.

Thanks for your help - both formulas work perfectly!