Tricky summation

[BKGLTS]

Hello,

I am needing a tricky (for me) summation of a row.

Row 1 - I have a very long row of percentage values ranging from 0 to 100. (Column A blank)​

Row 2 - I have a very long row of values ranging from 0 to Infinity. (Column A Blank)

Cell B4 - I have a fixed value of 0 to Infinity.

I need to create a summation in Cell A2 for Row 2.

The summation in Cell A2 needs to be derived by doing the following for each cell in its row with a value greater than 0.
So, starting with the first cell (B2) in Row 2 the formula looks like this:
=IF(B2>0,B2*($B$4*B1),0)

It would look like this for the second cell in the row (C2):

=IF(C2>0,C2*($B$4*C1),0)

and so on...​

Say for example there are 10 cells in Row 2 that have values greater than 0, and each of them results in the product value of 10 after applying the previous formula to it, the resulting sum value of A2 would be 100 because it adds all those values together for that row.


Another way to say it is it needs to take B2 multiplied by B1 & B4, then take C2

multiplied by C1 & B4,

then take D2 multiplied by D1 & B4, and so on... for all the cells in Row 2 that have a value greater than zero, and then add up the results of all those calculations and place the sum in cell A2.


​

<strike style="background-color: transparent; color: rgb(34, 34, 34); font-family: Verdana; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike>​

<strike></strike>
​

Tank you,
Brandon

 

[jtakw]

Hi,

If I understand correctly.
Change adjust cell/range references as needed.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1	2	3	4	0	6	7	8	9	10
2	336	2	3	4	5	6	7	8	0	10	11	12	13
3
4		8
Sheet363

Cell Formulas
Range	Formula
A2	=SUMIF(B2:ZZ2,">0",B1:ZZ1)*B4

 

[BKGLTS]

Jtakw,

Thank you for that, but that is not quite what I need.
Using your example it would go like this:

B2*B1*B4=16
C2*C1*B4=48

D2*D1*B4=96
E2*E1*B4=160
F2*F1*B4=0
G2*G1*B4=336
H2*H1*B4=448
I2*I1*B4=0
J2*J1*B4=720
K2*K1*B4=880
L2*L1*B4=0
M2*M1*B4=0

Then all those results are summed up in cell A2=2,704​

Thank you,
B

 

[jtakw]

Ahh, missed the part about Row 2, here's a corrected formula.

But then, I don't understand the Need for the condition to Test Row 2 to be >0, since if we are multiplying, if row 2 is 0, the product will be 0 anyway...

A2 formula tests for Row2 being 0,
A3 formula skips this test:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1	2	3	4	0	6	7	8	9	10
2	2704	2	3	4	5	6	7	8	0	10	11	12	13
3	2704
4		8
Sheet363

Cell Formulas
Range	Formula
A2	=SUMPRODUCT((B2:ZZ2>0)*B1:ZZ1*B2:ZZ2*B4)
A3	=SUMPRODUCT(B1:ZZ1*B2:ZZ2*B4)

 

[BKGLTS]

Thank you!
This appears to work great, but I am running into a snag.
Some of my Row 1 cells are set to blank because they contain formulas.
THis is causing the A2 formula you provided to error out.
You can replicate what I am seeing by sticking this formula in the L1 cell.
=IF(B4=0,1,"")

Is there something that can be done to ignore these blanks or utilize them as zeros?

Thank you!
B

 

[jtakw]

The easiest way to fix this problem is to Change your formulas in Row 1 to return a 0 (zero) instead of a Blank ( "" ), but this formula will work without that change, it is now an Array formula to be confirmed by CSE (Control, Shift, Enter), see instructions below:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1	2	3	4	0	6	7	8	9	10
2	2704	2	3	4	5	6	7	8	0	10	11	12	13
3
4		8
Sheet363

Cell Formulas
Range	Formula
L1	=""
M1	=""
A2	{=SUM((IF(ISNUMBER(B1:ZZ1),B1:ZZ1))*B2:ZZ2*B4)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[BKGLTS]

Thank you this works great!

 

[jtakw]

You're welcome, glad it's working for you.