Which formula should i use to sum some table and some criteria

[nasioedoeq]

Hi All,


I have problem in using excel formula to provide the correct result for my store performance.
I already googling some formula, both Excel and Google Sheet but it make me confused because some formula didn't work.
From that references I am using syntax Sumproduct, Sumif, Index and Match but it dont provide the correct result.


Any suggestion?


NB: Here is my sheet
[TABLE="width: 951"]
<tbody>[TR]
[TD]Store Location[/TD]
[TD]Item Name[/TD]
[TD]Sold[/TD]
[TD][/TD]
[TD]Category[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]Candy A[/TD]
[TD]21295[/TD]
[TD][/TD]
[TD]Candy[/TD]
[TD]Candy A[/TD]
[TD]Candy B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]Candy B[/TD]
[TD]3399[/TD]
[TD][/TD]
[TD]T Shirt[/TD]
[TD]T Shirt C[/TD]
[TD]T Shirt D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]T Shirt C[/TD]
[TD]5393[/TD]
[TD][/TD]
[TD]Shoes[/TD]
[TD]Shoes Y[/TD]
[TD]Shoes Z[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]T Shirt D[/TD]
[TD]508[/TD]
[TD][/TD]
[TD]Hat[/TD]
[TD]Hat L[/TD]
[TD]Hat M[/TD]
[TD]Hat N[/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]Shoes Y[/TD]
[TD]642[/TD]
[TD][/TD]
[TD]Gloves[/TD]
[TD]Gloves F[/TD]
[TD]Gloves G[/TD]
[TD]Gloves H[/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]Shoes Z[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Candy A[/TD]
[TD]194[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Candy B[/TD]
[TD]2910[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]T Shirt C[/TD]
[TD]664[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]T Shirt D[/TD]
[TD]760[/TD]
[TD][/TD]
[TD]Store Performance[/TD]
[TD]Category[/TD]
[TD]Sold[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Shoes Y[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]Wakanda[/TD]
[TD]Candy[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Shoes Z[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]Wakanda[/TD]
[TD]T Shirt[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Hat L[/TD]
[TD]83[/TD]
[TD][/TD]
[TD]Wakanda[/TD]
[TD]Shoes[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Hat M[/TD]
[TD]282[/TD]
[TD][/TD]
[TD]Wakanda[/TD]
[TD]Hat[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Hat N[/TD]
[TD]212[/TD]
[TD][/TD]
[TD]Wakanda[/TD]
[TD]Gloves[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Gloves F[/TD]
[TD]33440[/TD]
[TD][/TD]
[TD]Gotham[/TD]
[TD]Candy[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Gloves G[/TD]
[TD]185[/TD]
[TD][/TD]
[TD]Gotham[/TD]
[TD]T Shirt[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Gloves H[/TD]
[TD]22[/TD]
[TD][/TD]
[TD]Gotham[/TD]
[TD]Shoes[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Candy A[/TD]
[TD]501[/TD]
[TD][/TD]
[TD]Gotham[/TD]
[TD]Hat[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Candy B[/TD]
[TD]2289[/TD]
[TD][/TD]
[TD]Gotham[/TD]
[TD]Gloves[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]T Shirt C[/TD]
[TD]701[/TD]
[TD][/TD]
[TD]Smallville[/TD]
[TD]Candy[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]T Shirt D[/TD]
[TD]4941[/TD]
[TD][/TD]
[TD]Smallville[/TD]
[TD]T Shirt[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Shoes Y[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]Smallville[/TD]
[TD]Shoes[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Shoes Z[/TD]
[TD]2245[/TD]
[TD][/TD]
[TD]Smallville[/TD]
[TD]Hat[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Hat L[/TD]
[TD]5960[/TD]
[TD][/TD]
[TD]Smallville[/TD]
[TD]Gloves[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Hat M[/TD]
[TD]1091[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Hi. Try something like this:

=SUMIFS($C$2:$C$27,$A$2:$A$27,E12,$B$2:$B$27,"*"&F12&"*")

 

[nasioedoeq]

Hi Steve, thank you for your suggestion, it works very well if item name will use simple name and have exact name just like above example.

Apologize for missing information, actually each Item Name has complex combination name , that's why i have to refer to both table (Store Location and Category) and sum all of that into table Store Performance

Hope my explanation are clear and really need suggestion

Hi. Try something like this:

=SUMIFS($C$2:$C$27,$A$2:$A$27,E12,$B$2:$B$27,"*"&F12&"*")

 

[steve the fish]

Ah ok so thats a list of the names you want to sum?

=SUMPRODUCT(SUMIFS($C$2:$C$27,$A$2:$A$27,E12,$B$2:$B$27,INDEX($F$2:$O$6,MATCH(F12,$E$2:$E$6,0),0)))

 

[nasioedoeq]

Hi Steve, thanks again for your responses, but the formula dont give the correct result. Most of them is (0)
I don't have any idea and knowledge to edit or change any section.

Need advice

Ah ok so thats a list of the names you want to sum?

=SUMPRODUCT(SUMIFS($C$2:$C$27,$A$2:$A$27,E12,$B$2:$B$27,INDEX($F$2:$O$6,MATCH(F12,$E$2:$E$6,0),0)))

 

[steve the fish]

Based on the test data you gave what are the results?

 

[Michael M]

Whyy not put the data into a Pivot table

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][/tr]
[tr][td]

16​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

17​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

18​

[/td][td]Sum of Sold[/td][td]Column Labels[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

19​

[/td][td]Row Labels[/td][td]Gotham[/td][td]Smallville[/td][td]Wakanda[/td][td]Grand Total[/td][/tr]

[tr][td]

20​

[/td][td]Candy A[/td][td]

194​

[/td][td]

501​

[/td][td]

21295​

[/td][td]

21990​

[/td][/tr]

[tr][td]

21​

[/td][td]Candy B[/td][td]

2910​

[/td][td]

2289​

[/td][td]

3399​

[/td][td]

8598​

[/td][/tr]

[tr][td]

22​

[/td][td]Gloves F[/td][td]

33440​

[/td][td][/td][td][/td][td]

33440​

[/td][/tr]

[tr][td]

23​

[/td][td]Gloves G[/td][td]

185​

[/td][td][/td][td][/td][td]

185​

[/td][/tr]

[tr][td]

24​

[/td][td]Gloves H[/td][td]

22​

[/td][td][/td][td][/td][td]

22​

[/td][/tr]

[tr][td]

25​

[/td][td]Hat L[/td][td]

83​

[/td][td]

5960​

[/td][td][/td][td]

6043​

[/td][/tr]

[tr][td]

26​

[/td][td]Hat M[/td][td]

282​

[/td][td]

1091​

[/td][td][/td][td]

1373​

[/td][/tr]

[tr][td]

27​

[/td][td]Hat N[/td][td]

212​

[/td][td][/td][td][/td][td]

212​

[/td][/tr]

[tr][td]

28​

[/td][td]Shoes Y[/td][td]

0​

[/td][td]

7​

[/td][td]

642​

[/td][td]

649​

[/td][/tr]

[tr][td]

29​

[/td][td]Shoes Z[/td][td]

0​

[/td][td]

2245​

[/td][td]

10​

[/td][td]

2255​

[/td][/tr]

[tr][td]

30​

[/td][td]T Shirt C[/td][td]

664​

[/td][td]

701​

[/td][td]

5393​

[/td][td]

6758​

[/td][/tr]

[tr][td]

31​

[/td][td]T Shirt D[/td][td]

760​

[/td][td]

4941​

[/td][td]

508​

[/td][td]

6209​

[/td][/tr]

[tr][td]

32​

[/td][td]Grand Total[/td][td]

38752​

[/td][td]

17735​

[/td][td]

31247​

[/td][td]

87734​

[/td][/tr]

[tr][td]

33​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[nasioedoeq]

Here is the result (i attached the table as well)

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="118"><col width="72"><col width="89"><col width="89"><col width="100"></colgroup><tbody>[TR]
[TD]Store Location[/TD]
[TD]Item Name[/TD]
[TD]Item Sold[/TD]
[TD][/TD]
[TD]Category[/TD]
[TD]Item Name[/TD]
[TD]Item Name[/TD]
[TD]Item Name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]Candy A[/TD]
[TD="align: right"]21295[/TD]
[TD][/TD]
[TD]Candy[/TD]
[TD]Candy A[/TD]
[TD]Candy B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]Candy B[/TD]
[TD="align: right"]3399[/TD]
[TD][/TD]
[TD]T Shirt[/TD]
[TD]T Shirt C[/TD]
[TD]T Shirt D[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]T Shirt C[/TD]
[TD="align: right"]5393[/TD]
[TD][/TD]
[TD]Shoes[/TD]
[TD]Shoes Y[/TD]
[TD]Shoes Z[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]T Shirt D[/TD]
[TD="align: right"]508[/TD]
[TD][/TD]
[TD]Hat[/TD]
[TD]Hat L[/TD]
[TD]Hat M[/TD]
[TD]Hat N[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]Shoes Y[/TD]
[TD="align: right"]642[/TD]
[TD][/TD]
[TD]Gloves[/TD]
[TD]Gloves F[/TD]
[TD]Gloves G[/TD]
[TD]Gloves H[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]Shoes Z[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Candy A[/TD]
[TD="align: right"]194[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Candy B[/TD]
[TD="align: right"]2910[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]T Shirt C[/TD]
[TD="align: right"]664[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]T Shirt D[/TD]
[TD="align: right"]760[/TD]
[TD][/TD]
[TD]Store Performance[/TD]
[TD]Category[/TD]
[TD]Category Sold[/TD]
[TD]Category Sold[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Shoes Y[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Wakanda[/TD]
[TD]Candy[/TD]
[TD="align: right"]3399[/TD]
[TD="align: right"]24694[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Shoes Z[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Wakanda[/TD]
[TD]T Shirt[/TD]
[TD="align: right"]508[/TD]
[TD="align: right"]5901[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Hat L[/TD]
[TD="align: right"]83[/TD]
[TD][/TD]
[TD]Wakanda[/TD]
[TD]Shoes[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]652[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Hat M[/TD]
[TD="align: right"]282[/TD]
[TD][/TD]
[TD]Wakanda[/TD]
[TD]Hat[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Hat N[/TD]
[TD="align: right"]212[/TD]
[TD][/TD]
[TD]Wakanda[/TD]
[TD]Gloves[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Gloves F[/TD]
[TD="align: right"]33440[/TD]
[TD][/TD]
[TD]Gotham[/TD]
[TD]Candy[/TD]
[TD="align: right"]2910[/TD]
[TD="align: right"]3104[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Gloves G[/TD]
[TD="align: right"]185[/TD]
[TD][/TD]
[TD]Gotham[/TD]
[TD]T Shirt[/TD]
[TD="align: right"]760[/TD]
[TD="align: right"]1424[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Gloves H[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD]Gotham[/TD]
[TD]Shoes[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Candy A[/TD]
[TD="align: right"]501[/TD]
[TD][/TD]
[TD]Gotham[/TD]
[TD]Hat[/TD]
[TD="align: right"]282[/TD]
[TD="align: right"]577[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Candy B[/TD]
[TD="align: right"]2289[/TD]
[TD][/TD]
[TD]Gotham[/TD]
[TD]Gloves[/TD]
[TD="align: right"]185[/TD]
[TD="align: right"]33647[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]T Shirt C[/TD]
[TD="align: right"]701[/TD]
[TD][/TD]
[TD]Smallville[/TD]
[TD]Candy[/TD]
[TD="align: right"]2289[/TD]
[TD="align: right"]2790[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]T Shirt D[/TD]
[TD="align: right"]4941[/TD]
[TD][/TD]
[TD]Smallville[/TD]
[TD]T Shirt[/TD]
[TD="align: right"]4941[/TD]
[TD="align: right"]5642[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Shoes Y[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]Smallville[/TD]
[TD]Shoes[/TD]
[TD="align: right"]2245[/TD]
[TD="align: right"]2252[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Shoes Z[/TD]
[TD="align: right"]2245[/TD]
[TD][/TD]
[TD]Smallville[/TD]
[TD]Hat[/TD]
[TD="align: right"]1091[/TD]
[TD="align: right"]7051[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Hat L[/TD]
[TD="align: right"]5960[/TD]
[TD][/TD]
[TD]Smallville[/TD]
[TD]Gloves[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Hat M[/TD]
[TD="align: right"]1091[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

FYI, the red color use this formula =SUMPRODUCT(SUMIFS($C$2:$C$27,$A$2:$A$27,E12,$B$2:$B$27,INDEX($F$2:$O$6,MATCH(F12,$E$2:$E$6,0),0)))
while the blue color use your first formula =SUMIFS($C$2:$C$27,$A$2:$A$27,E12,$B$2:$B$27,"*"&F12&"*")

Actually the red color give the correct result, but if I implement the formula at the original Item Name, which is much more complicated (long character and unique) then it will not give the correct result.

Please help

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style><style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Based on the test data you gave what are the results?

 

[nasioedoeq]

I am not using pivot because I need to know the category sold by each store location, while each category consist of some Item

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 118px"><col width="72"><col width="89"></colgroup><tbody>[TR]
[TD]Store Performance[/TD]
[TD]Category[/TD]
[TD]Category Sold[/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]Candy[/TD]
[TD="align: center"]?[/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]T Shirt[/TD]
[TD="align: center"]?[/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]Shoes[/TD]
[TD="align: center"]?[/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]Hat[/TD]
[TD="align: center"]?[/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]Gloves[/TD]
[TD="align: center"]?[/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Candy[/TD]
[TD="align: center"]?[/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]T Shirt[/TD]
[TD="align: center"]?[/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Shoes[/TD]
[TD="align: center"]?[/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Hat[/TD]
[TD="align: center"]?[/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Gloves[/TD]
[TD="align: center"]?[/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Candy[/TD]
[TD="align: center"]?[/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]T Shirt[/TD]
[TD="align: center"]?[/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Shoes[/TD]
[TD="align: center"]?[/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Hat[/TD]
[TD="align: center"]?[/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Gloves[/TD]
[TD="align: center"]?[/TD]
[/TR]
</tbody>[/TABLE]

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Whyy not put the data into a Pivot table

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]Sum of Sold[/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]Row Labels[/TD]
[TD]Gotham[/TD]
[TD]Smallville[/TD]
[TD]Wakanda[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]Candy A[/TD]
[TD]

194​

[/TD]
[TD]

501​

[/TD]
[TD]

21295​

[/TD]
[TD]

21990​

[/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD]Candy B[/TD]
[TD]

2910​

[/TD]
[TD]

2289​

[/TD]
[TD]

3399​

[/TD]
[TD]

8598​

[/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD]Gloves F[/TD]
[TD]

33440​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

33440​

[/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD]Gloves G[/TD]
[TD]

185​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

185​

[/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD]Gloves H[/TD]
[TD]

22​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

22​

[/TD]
[/TR]
[TR]
[TD]

25​

[/TD]
[TD]Hat L[/TD]
[TD]

83​

[/TD]
[TD]

5960​

[/TD]
[TD][/TD]
[TD]

6043​

[/TD]
[/TR]
[TR]
[TD]

26​

[/TD]
[TD]Hat M[/TD]
[TD]

282​

[/TD]
[TD]

1091​

[/TD]
[TD][/TD]
[TD]

1373​

[/TD]
[/TR]
[TR]
[TD]

27​

[/TD]
[TD]Hat N[/TD]
[TD]

212​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

212​

[/TD]
[/TR]
[TR]
[TD]

28​

[/TD]
[TD]Shoes Y[/TD]
[TD]

0​

[/TD]
[TD]

7​

[/TD]
[TD]

642​

[/TD]
[TD]

649​

[/TD]
[/TR]
[TR]
[TD]

29​

[/TD]
[TD]Shoes Z[/TD]
[TD]

0​

[/TD]
[TD]

2245​

[/TD]
[TD]

10​

[/TD]
[TD]

2255​

[/TD]
[/TR]
[TR]
[TD]

30​

[/TD]
[TD]T Shirt C[/TD]
[TD]

664​

[/TD]
[TD]

701​

[/TD]
[TD]

5393​

[/TD]
[TD]

6758​

[/TD]
[/TR]
[TR]
[TD]

31​

[/TD]
[TD]T Shirt D[/TD]
[TD]

760​

[/TD]
[TD]

4941​

[/TD]
[TD]

508​

[/TD]
[TD]

6209​

[/TD]
[/TR]
[TR]
[TD]

32​

[/TD]
[TD]Grand Total[/TD]
[TD]

38752​

[/TD]
[TD]

17735​

[/TD]
[TD]

31247​

[/TD]
[TD]

87734​

[/TD]
[/TR]
[TR]
[TD]

33​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[nasioedoeq]

Here is the result (by using your latest formula)

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 118px"><col width="72"><col width="89"></colgroup><tbody>[TR]
[TD]Store Performance[/TD]
[TD]Category[/TD]
[TD]Category Sold[/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]Candy[/TD]
[TD="align: right"]3399[/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]T Shirt[/TD]
[TD="align: right"]508[/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]Shoes[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]Hat[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Wakanda[/TD]
[TD]Gloves[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Candy[/TD]
[TD="align: right"]2910[/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]T Shirt[/TD]
[TD="align: right"]760[/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Shoes[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Hat[/TD]
[TD="align: right"]282[/TD]
[/TR]
[TR]
[TD]Gotham[/TD]
[TD]Gloves[/TD]
[TD="align: right"]185[/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Candy[/TD]
[TD="align: right"]2289[/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]T Shirt[/TD]
[TD="align: right"]4941[/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Shoes[/TD]
[TD="align: right"]2245[/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Hat[/TD]
[TD="align: right"]1091[/TD]
[/TR]
[TR]
[TD]Smallville[/TD]
[TD]Gloves[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Based on the test data you gave what are the results?