I have below data and I need Week wise and Name wise Amount through a dynamic formula. Any help in this regard is highly appreciated.

CodePudding user response：

you must reorganize your data into 3 columns week, name, and amount, and then you can use the pivot table to calculate the amount for each week per person

CodePudding user response：

You can try below formula.

=SUMPRODUCT(--($A$14:$F$16=$B2)*--($A$13:$F$13=$A2)*(IF(ISNUMBER($B$14:$G$16),$B$14:$G$16,0)))