how to multiply the a const value with the sumproduct

[kiran5388]

TodayCalcSample.xlsx
	A	B	C	D	E	F	G
1	ProductA	2	2	2	2
2	ProductA	2		1
3	ProductB	2	2	0
4	ProductA		2	1
5	ProductC	2	1	2
6
7
8
9	Expected result is	B1*C1*D1*E1
10		B2*C2*D2*E1
11		B3*C3*D3*E1
12		B4*C4*D4*E1
13		B5*C5*D5*E1
14		SUM(B9:B13)
15		28
16	Criteria: if BLANK in any row, ignore the row in calculation
17	I have done SUMPRODUCT for B,C,D and how to multiply E1 to each row
18		12
19
Sheet3

Cell Formulas
Range	Formula
B18	B18	=SUMPRODUCT((B1:B5<>"")*1,(C1:C5<>"")*1,(D1:D5<>"")*1,B1:B5,C1:C5,D1:D5)

 

[jasonb75]

Check your expected results, what should happen with the blanks in C2 and D4? It appears that you have only allowed for the blanks in column E.

It would help if you also provided the expected calculation of each individual row, based on your logic I get a total of 32, not 28.

 

[kiran5388]

Check your expected results, what should happen with the blanks in C2 and D4? It appears that you have only allowed for the blanks in column E.

It would help if you also provided the expected calculation of each individual row, based on your logic I get a total of 32, not 28.

So, from B,C,D any cell is Blank then ignore the whole row. Therefore Row1 and Row5 are good for the calculation, so Row1 = 2*2*2*2=16 , Row5 = 2*1*2*2=8. Row1+Row5= 16+8=24, my bad I mentioned 28. Anyways, I figured it out the solution here is the formula =SUMPRODUCT((B1:B5<>"")*1,(C1:C5<>"")*1,(D1:D5<>"")*1,E1*(B1:B5),C1:C5,D1:D5) which equal to 24

 

[jasonb75]

You don't need the logical tests, empty cells will default to 0 anyway.

=SUMPRODUCT(B1:B5*C1:C5*D1:D5*E1)

 

[Dave Patton]

What is expected result?

T202110b.xlsm
	A	B	C	D	E	F	G
1	ProductA	2	2	2	2	16	TRUE
2	ProductA	2		1		0	FALSE
3	ProductB	2	2	0		0	TRUE
4	ProductA		2	1		0	FALSE
5	ProductC	2	1	2		8	TRUE
6						24	24
7							24
1c

Cell Formulas
Range	Formula
F1:F5	F1	=(COUNT(B1:D1)=3)*PRODUCT(B1:D1)*$E$1
F6	F6	=SUM(F1:F5)
G1:G5	G1	=COUNT(B1:D1)=3
G6	G6	=SUMPRODUCT(--(G1:G5=TRUE),B1:B5,C1:C5,D1:D5)*E1
G7	G7	=SUMPRODUCT(B1:B5,C1:C5,D1:D5)*E1

 

[kiran5388]

You don't need the logical tests, empty cells will default to 0 anyway.

=SUMPRODUCT(B1:B5*C1:C5*D1:D5*E1)

Thanks for the reply, Actually, I need those logical test for the average, I dnt consider the blank cells in calculation and also its needed for multiplying E1 which are not blank, if not it will become 0. Anyways the answer should be 24 for the above.

 

[kiran5388]

What is expected result?

T202110b.xlsm
	A	B	C	D	E	F	G
1	ProductA	2	2	2	2	16	TRUE
2	ProductA	2		1		0	FALSE
3	ProductB	2	2	0		0	TRUE
4	ProductA		2	1		0	FALSE
5	ProductC	2	1	2		8	TRUE
6						24	24
7							24
1c

Cell Formulas
Range	Formula
F1:F5	F1	=(COUNT(B1:D1)=3)*PRODUCT(B1:D1)*$E$1
F6	F6	=SUM(F1:F5)
G1:G5	G1	=COUNT(B1:D1)=3
G6	G6	=SUMPRODUCT(--(G1:G5=TRUE),B1:B5,C1:C5,D1:D5)*E1
G7	G7	=SUMPRODUCT(B1:B5,C1:C5,D1:D5)*E1

Thanks for the solution

 

[jasonb75]

Actually, I need those logical test for the average, I dnt consider the blank cells in calculation and also its needed for multiplying E1 which are not blank, if not it will become 0. Anyways the answer should be 24 for the above.

With the formula that I suggested the answer is 24 for the sample provided, the way that the formula works the empty cells will be converted to 0 as part of the calculation.

If you dismiss something that you don't understand because you think it is incorrect then you will never learn anything new.

 

[kiran5388]

With the formula that I suggested the answer is 24 for the sample provided, the way that the formula works the empty cells will be converted to 0 as part of the calculation.

If you dismiss something that you don't understand because you think it is incorrect then you will never learn anything new.

yeah I understood, and got your point. Thanks