Make =Sumproduct ignore blank cells instead of returning "#VALUE!"

[LilMill]

Tinglev rute.xlsx
	D	E	F	G	H	I	J	K	L	M	N
2	1 varm		1 varm	1 varm	1 varm	1 kold	1 varm		#VALUE!	#VALUE!	#VALUE!
3	1 varm	1 varm	1 varm	1 varm	0	0	0		4	0	260,00 kr.
4	2 kold	2 kold	2 kold	0	0	0	1 varm		1	6	455,00 kr.
5	1 varm	1 varm	1 varm	1 varm	1 varm	1 varm	1 varm		7	0	455,00 kr.
Uge

Cell Formulas
Range	Formula
L2:L3	L2	=SUMPRODUCT((RIGHT(D2:J2,4)=$L$1)*(LEFT(D2:J2,1)))
M2	M2	=SUMPRODUCT((RIGHT(D2:J2,4)=$M$1)*(LEFT(D2:J2,1)))
N2:N5	N2	=SUM(L2:M2)*65
M3:M5	M3	=SUMPRODUCT((RIGHT(D3:J3,4)=$M$1)*(LEFT(D3:J3,1)))
L4:L5	L4	=SUMPRODUCT((RIGHT(D4:J4,4)=$L$1)*(LEFT(D4:J4,1)))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[cyrilbrd]

Hi, what is in Row 1 Columns L,M and N?

 

[cyrilbrd]

excel.xlsx
	D	E	F	G	H	I	J	K	L	M	N
1									varm	kold
2	1 varm		1 varm	1 varm	1 varm	1 kold	1 varm		5	1	390
3	1 varm	1 varm	1 varm	1 varm	0	0	0		4	0	260
4	2 kold	2 kold	2 kold	0	0	0	1 varm		1	4	325
5	1 varm	1 varm	1 varm	1 varm	1 varm	1 varm	1 varm		7	0	455
LilMill

Cell Formulas
Range	Formula
L2:L5	L2	=SUMPRODUCT(IF((D2:J2)<>0,(RIGHT(D2:J2,4)=$L$1)*(LEFT(D2:J2,1))))
M2:M5	M2	=SUMPRODUCT(IF((E2:K2)<>0,(RIGHT(E2:K2,4)=$M$1)*(LEFT(E2:K2,1))))
N2:N5	N2	=SUM(L2:M2)*65

 

[LilMill]

Hi, what is in Row 1 Columns L,M and N?

Same as in the rows under, it is just summing the

 

[LilMill]

excel.xlsx
	D	E	F	G	H	I	J	K	L	M	N
1									varm	kold
2	1 varm		1 varm	1 varm	1 varm	1 kold	1 varm		5	1	390
3	1 varm	1 varm	1 varm	1 varm	0	0	0		4	0	260
4	2 kold	2 kold	2 kold	0	0	0	1 varm		1	4	325
5	1 varm	1 varm	1 varm	1 varm	1 varm	1 varm	1 varm		7	0	455
LilMill

Cell Formulas
Range	Formula
L2:L5	L2	=SUMPRODUCT(IF((D2:J2)<>0,(RIGHT(D2:J2,4)=$L$1)*(LEFT(D2:J2,1))))
M2:M5	M2	=SUMPRODUCT(IF((E2:K2)<>0,(RIGHT(E2:K2,4)=$M$1)*(LEFT(E2:K2,1))))
N2:N5	N2	=SUM(L2:M2)*65

I cant get this to work

 

[cyrilbrd]

What issue are you facing?

 

[LilMill]

I just says "There is a problem with this formula"

 

[cyrilbrd]

Since the formula is the same for both adjectives (hot and cold / varm og kold), maybe you could consider using $ to change the cell reference.

excel.xlsx
	D	E	F	G	H	I	J	K	L	M	N
1									varm	kold	resultater
2	1 varm		1 varm	1 varm	1 varm	1 kold	1 varm		5	1	390 kr.
3	1 varm	1 varm	1 varm	1 varm	0	0	0		4	0	260 kr.
4	2 kold	2 kold	2 kold	0	0	0	1 varm		1	6	455 kr.
5	1 varm	1 varm	1 varm	1 varm	1 varm	1 varm	1 varm		7	0	455 kr.
LilMill

Cell Formulas
Range	Formula
L2:L5	L2	=SUMPRODUCT(IF(($D2:$J2)<>0,(RIGHT($D2:$J2,4)=$L$1)*(LEFT($D2:$J2,1))))
M2:M5	M2	=SUMPRODUCT(IF(($D2:$J2)<>0,(RIGHT($D2:$J2,4)=$M$1)*(LEFT($D2:$J2,1))))
N2:N5	N2	=SUM(L2:M2)*65

 

[LilMill]

 

[cyrilbrd]

Sorry I am not sure if it is because of the European semicolon formula ; instead of commas ,
try
=SUMPRODUKT(HVIS(($D2:$J2)<>0;(HØJRE($D2:$J2,4)=$L$1)*(VENSTRE($D2:$J2,1))))