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)

 

[hrayani]

There is an error in the formula range B12:B28 shoud be B12:B51

=SUMPRODUCT((A12:A51="productB")*1,(B12:B51<>"")*1,(C12:C51<>"")*1,(D12:D51<>"")*1,(E12:E51<>"")*1,E12:E51,B12:B51+C12:C51+D12:D51)

 

[kiran5388]

There is an error in the formula range B12:B28 shoud be B12:B51

=SUMPRODUCT((A12:A51="productB")*1,(B12:B51<>"")*1,(C12:C51<>"")*1,(D12:D51<>"")*1,(E12:E51<>"")*1,E12:E51,B12:B51+C12:C51+D12:D51)

after fixing that, still shows the error

 

[Michael M]

I deleted, then reinserted the formula, and it works fine for me...

=SUMPRODUCT((A12:A51="productB")*1,(B12:B51<>"")*1,(C12:C51<>"")*1,(D12:D51<>"")*1,(E12:E51<>"")*1,E12:E51,B12:B51+C12:C51+D12:D51)

 

[kiran5388]

I deleted, then reinserted the formula, and it works fine for me...

=SUMPRODUCT((A12:A51="productB")*1,(B12:B51<>"")*1,(C12:C51<>"")*1,(D12:D51<>"")*1,(E12:E51<>"")*1,E12:E51,B12:B51+C12:C51+D12:D51)

for me its not working, I see the error comes on row 31.

 

[kiran5388]

Anyone had a solution for this?

 

[kiran5388]

Any alternative for this?

 

[bebo021999]

It works for me.
May be for some reasons, your blank cells in row 31 are not actual blank.
Try to delete those cells, or *1 to last statement (B+C+D)*1

=SUMPRODUCT((A12:A51="productB")*1,(B12:B51<>"")*1,(C12:C51<>"")*1,(D12:D51<>"")*1,(E12:E51<>"")*1,E12:E51,(B12:B51+C12:C51+D12:D51)*1)

 

[bebo021999]

If #8 still does not work, try ((B12:B51)*1 + (C12:C51)*1+(D12:D51)*1) instead.

 

[kiran5388]

If #8 still does not work, try ((B12:B51)*1 + (C12:C51)*1+(D12:D51)*1) instead.

sorry, both didn't work. I cant delete the cells manually, actually those are generated programmatically.