Sum according to column content

[Sunny_bunny]

Good day.

Can you please help me with a question, as screenshot and attachment?

With the given original data, I wish to have some formulas to calculate, as shown in Table B (which is from manual calculation).

In columns H to M, they show #N/A.

Where went wrong and how can I correct these errors? Thank you.

Formula in cell H20: =SUM(($E$6:$E$14=$E20)*($F$6:$F$14=RIGHT(PHONETIC(OFFSET($H$18,,,,COLUMNS($A:A))),2))*($H$5:$I$5=H$19)*$H$6:$I$14)
(drag right and down to fill)

(sorry that I am not able to use the mini-sheet function. please find here for the link to the Excel file)

Thank you!

 

[HongRu]

cell H20 is

=SUMPRODUCT(($E$6:$E$14=$E20)*($F$6:$F$14=MID(H$18,10,20))*$H$6:$H$14)

cell I20 is

=SUMPRODUCT(($E$6:$E$14=$E20)*($F$6:$F$14=MID(H$18,10,20))*$I$6:$I$14)

copy H20:I20 to H20:M22
done.

 

[shinigamilight]

=SUMIFS($H$6:$H$14,$F$6:$F$14,"Bakery",$E$6:$E$14,E20)

 

[Sunny_bunny]

cell H20 is

=SUMPRODUCT(($E$6:$E$14=$E20)*($F$6:$F$14=MID(H$18,10,20))*$H$6:$H$14)

cell I20 is

=SUMPRODUCT(($E$6:$E$14=$E20)*($F$6:$F$14=MID(H$18,10,20))*$I$6:$I$14)

copy H20:I20 to H20:M22
done.

@HongRu, fantastic! thank you for the brilliant help!