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.

 

[LilMill]

Still nothing

 

[cyrilbrd]

what about that=SUMPRODUCT(IF(($D2:$J2)<>0,(RIGHT($D2:$J2,4)=$L$1)*(LEFT($D2:$J2,1)),""))

 

[Peter_SSs]

Try this, copied across and down (may need to change commas to semicolons?)

A_MrExcel.xlsm
	D	E	F	G	H	I	J	K	L	M
1									Varm	Kold
2	1 varm		1 varm	1 varm	1 varm	1 kold	1 varm		5	1
3	1 varm	1 varm	1 varm	1 varm	0	0	0		4	0
4	2 kold	2 kold	2 kold	0	0	0	1 varm		1	6
5	1 varm	1 varm	1 varm	1 varm	1 varm	1 varm	1 varm		7	0
SP

Cell Formulas
Range	Formula
L2:M5	L2	=SUMPRODUCT((RIGHT($D2:$J2,4)=L$1)*(LEFT(0&$D2:$J2,2)))

 

[LilMill]

Both did work now, but instead of “#VALUE!” It says “#NAME?”

 

[zinzah]

just a thought...what if you wrapped the whole formula with "=IFERROR(SUMPRODUKT(HVIS(($D2:$J2)<>0;(HØJRE($D2:$J2,4)=$L$1)*(VENSTRE($D2:$J2,1)))),"")

 

[LilMill]

just a thought...what if you wrapped the whole formula with "=IFERROR(SUMPRODUKT(HVIS(($D2:$J2)<>0;(HØJRE($D2:$J2,4)=$L$1)*(VENSTRE($D2:$J2,1)))),"")

It says the same

 

[Fluff]

If you are using a Danish version try Peter's formula like
=SUMPRODUKT((HØJRE($D2:$J2;4)=L$1)*(VENSTRE(0&$D2:$J2;2)))

 

[LilMill]

If you are using a Danish version try Peter's formula like
=SUMPRODUKT((HØJRE($D2:$J2;4)=L$1)*(VENSTRE(0&$D2:$J2;2)))

That worked, thank you!

 

[Fluff]

You're welcome & thanks for the feedback.
But it's Peter_SSs formula, I just translated it