sumproduct and sum not working

[kiran5388]

Table 1 result is fine, but the Table 2 is not working. Please help.

TodayCalcSample.xlsx
	A	B	C	D	E	F	G	H	I
1	Table 1
2	productC	4	0	1	2
3	productA	16		0	2
4	productB	0	0	0	2
5	productA		0	0	2
6	productB	0	1	0	2
7	productA	2	0	0	2		Result=	16
8
9
10
11	Table 2
12	productA	4	0	1	2
13	productA	16	0	0	2
14	productB	0	0	0	2
15	productA	0	0	0	2
16	productB	0	1	0	2		Result=	#VALUE!
17	productA	24	0	0	2
18	productB	2		0	2		Expected =	26
19	productB	4	0	5	2
20	productA	1	0	0	2
21	productA	0	0	0	2
22	productA	0	0	0	2
23	productA	0	0	0	2
24	productA	0	5	0	2
25	productA	7	0	0	2
26	productA	0	2	0	2
27	productB	3	0	0	2
28	productA	0	0	0	2
29	productA	12	0	0	2
30	productA	2	0	1	2
31	productA				2
32	productA	0	0	0	2
33	productA	6	0	0	2
34	productA	3	8	2	2
35	productA	0	0	0	2
36	productA	1	0	0	2
37	productA	7	0	0	2
38	productA	0	0	0	2
39	productA	4	0	0	2
40	productA	6	0	0	2
41	productA	3	0	0	2
42	productA	1	0	0	2
43	productA	4	0	1	2
44	productA	1	0	0	2
45	productA	65	0	0	2
46	productA	1	0	0	2
47	productA	0	0	4	2
48	productA	1	0	0	2
49	productA	86	0	0	2
50	productA	1	0	0	2
51	productA	1	0	0	2
52
Sheet4

Cell Formulas
Range	Formula
H7	H7	=SUMPRODUCT((E2:E7<>"")*1,(B2:B7<>"")*1,(C2:C7<>"")*1,(D2:D7<>"")*1,E2:E7,B2:B7+C2:C7+D2:D7)
H16	H16	=SUMPRODUCT((A12:A51="productB")*1,(B12:B28<>"")*1,(C12:C51<>"")*1,(D12:D51<>"")*1,(E12:E51<>"")*1,E12:E51,B12:B51+C12:C51+D12:D51)

 

[bebo021999]

Try to add zero value to your B12:E51 by:
Copy a blank cell (copy zero)
select B12:B51
Paste/paste special: Operation: Add

Does it work?

 

[kiran5388]

Try to add zero value to your B12:E51 by:
Copy a blank cell (copy zero)
select B12:B51
Paste/paste special: Operation: Add

Does it work?

Manually if I enter the values and the blank cell the formula works fine, I don't know programmatically the blanks cells are considered in different datatype maybe

 

[bebo021999]

That why I suggest to "wake those blank cells up" by add a "zero" value to automaticallyas per #11.

 

[kiran5388]

That why I suggest to "wake those blank cells up" by add a "zero" value to automaticallyas per #11.

I Like your suggestion, but if the blanks are made into zero, then the row which was empty now will gets into calculation and the result will be different than expected

 

[MARK858]

Try selecting the column, then click the Data tab, click Text to Columns, click Next, check that none of the options is checked and click Next, click Finish.

Does that make a difference?

 

[kiran5388]

Try selecting the column, then click the Data tab, click Text to Columns, click Next, check that none of the options is checked and click Next, click Finish.

Does that make a difference?

anyways thanks, I cant do it manually, the program should handle that. I got the solution, the empty cells are being string DataTypes, I made those to null by program. that seems to be working.

 

[kiran5388]

That why I suggest to "wake those blank cells up" by add a "zero" value to automaticallyas per #11.

anyways thanks, I cant do it manually, the program should handle that. I got the solution, the empty cells are being considered as string DataTypes, I made those to null by program. now I got the answer seems to be working.

 

[kiran5388]

sol

Any alternative for this?

solved myself by changing the data type to null programmatically

 

[MARK858]

Glad you got it sorted (btw there is no reason why you couldn't record the actions that I proposed in post 15 to get the code if you wanted it done via code)

 

[bebo021999]

I Like your suggestion, but if the blanks are made into zero, then the row which was empty now will gets into calculation and the result will be different than expected

Sorry, I mean copy blank cell (null value, not zero) to a null cell. Destination cell does not change.
Test by: Assum A1,B1 is blank. In A2, use: =A1="" =>="TRUE"
Copy B1, paste=Add to A1. A2 still=TRUE. It means A1 still="null".