Sumproduct with Table Rows doesn't work

[Nicha]

I have a table wher I'm trying to use Sumproduct by row, and an Fixed Range out of the Table Range. The Table Range is [B4:I5] and I need to use SumProduct on [H] Column that must consider the current Row and the Price Values, on Fixed Range, [C3:G3], to calculate the Total on Column [H].

The problem is, when I inser values in the next row - Number 6 - The SumProduct Returns #Value. Can Anyone help please?

That Formula, must work for every new row, inserted in that table.

PEDIDOS_AGUAS.xlsx
	B	C	D	E	F	G	H	I
2	Encomenda :	1	0	0	0	0	1
3	Price :	2,40 €	2,80 €	5,20 €	2,80 €	4,00 €
4	Nome	Fonte Fraga/6	Caramulo/6	Caramulo/24	Penacova/6	Monchique/6	Total €	Pago
5	Leopoldo Fernandes	1					2,4
Sheet1

Cell Formulas
Range	Formula
C2	C2	=SUBTOTAL(9,Sheet1!$C$5:$C$5)
D2	D2	=SUBTOTAL(9,Sheet1!$D$5:$D$5)
E2	E2	=SUBTOTAL(9,Sheet1!$E$5:$E$5)
F2	F2	=SUBTOTAL(9,Sheet1!$F$5:$F$5)
G2	G2	=SUBTOTAL(9,Sheet1!$G$5:$G$5)
H2	H2	=SUM(C2:G2)
H5	H5	=SUMPRODUCT(Sheet1!$C5:$G5,$C$3:$G$3)

I have one more question. It's possible to put on column [I5] a Check Mark that could be Filtered, indicating that a person has already Payed?
When Cell is empty - means that the Total has not being payed; When the Cell has the CheckMArk - means that the Parson has already payed.


The Idea, as with the Sumproduct, is that when we insert values in a new row Both the Sumproduct and the CheckMark shuld be possible to insert.

Thank's in advance

 

[Nicha]

When I insert number 3 on Row 6 the Sumproduct gives error, and the formula shows [=SUMPRODUCT(Sheet1!$C6:$G7;$C$3:$G$3)] instead of[=SUMPRODUCT(Sheet1!$C6:$G6;$C$3:$G$3)]. Sheet1!$C6:$G7 Shoul be Sheet1!$C6:$G6

 

[Sergius]

Hi!
Replace $C6:$G6 with C$6:G$6

 

[Sergius]

I apologize, I wrote something wrong. Is that what you wanted?

Book1.xlsm
	B	C	D	E	F	G	H	I
2	Encomenda :	1	0	0	0	0	1
3	Price :	2,40 €	2,80 €	5,20 €	2,80 €	4,00 €
4	Nome	Fonte Fraga/6	Caramulo/6	Caramulo/24	Penacova/6	Monchique/6	Total €	Pago
5	Leopoldo Fernandes	1					2,4	ü
6	Test						0
Sheet16

Cell Formulas
Range	Formula
C2	C2	=SUBTOTAL(9,Sheet16!$C$5:$C$6)
D2	D2	=SUBTOTAL(9,Sheet16!$D$5:$D$6)
E2	E2	=SUBTOTAL(9,Sheet16!$E$5:$E$6)
F2	F2	=SUBTOTAL(9,Sheet16!$F$5:$F$6)
G2	G2	=SUBTOTAL(9,Sheet16!$G$5:$G$6)
H2	H2	=SUM(C2:G2)
H5:H6	H5	=SUMPRODUCT(Sheet16!$C5:$G5,$C$3:$G$3)
I5:I6	I5	=IF([@[Total €]]>0,"ü",IF([@[Total €]]=0,""))

For the Pago column cells, select the Wingdings font to draw the check mark.

 

[Nicha]

Hi!
Replace $C6:$G6 with C$6:G$6

Thank you @*Sergius.

Using your sugestion on formula (tables first row [H5], when I inser a valur on a new row the formula end's like the image below.

Price is the Name I gave to the Price Range.

That doesn't work too. Is there any solution for a table? Or the solution is to convert table to excel range and use Autofill for the formula?

Many thank's to you.

 

[Sergius]

Did you see my post #4? It's fixed and shows what you wanted. Or is there something wrong? Let me know.

 

[Nicha]

Did you see my post #4? It's fixed and shows what you wanted. Or is there something wrong? Let me know.

The Problem is that initialy we only have row (5) with the formula [=SUMPRODUCT(Sheet16!$C5:$G5,$C$3:$G$3)] on H5. But if wu enter data on row (6)
the tableRange [H6] end up with the Formula [=SUMPRODUCT(Sheet16!$C5:$G7,$C$3:$G$3)], and the Result : #Value.
I don't understand how excel don't assume on [H6] the formula [=SUMPRODUCT(Sheet16!$C6:$G6,$C$3:$G$3)].

Thank you @*Sergius once more.

 

[Sergius]

Replace Sheet16 with the name of your sheet.

 

[Nicha]

Replace Sheet16 with the name of your sheet.

Hi @Sergius

Thank you for your help.
I can't make it work. I solved the problem Converting the Table to Range, Applyed the formula autofilling to 5 rows, and then converted back to table.

When I initially create the formula - as a table - the formula was : [=SUMPRODUCT(Table1[[Fonte Fraga/6]:[Monchique/6]];Price)], and when insert new values in table that doesn't worked too.

Thank you once more.