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

Store Location	Item Name	Sold		Category
Wakanda	Candy A	21295		Candy	Candy A	Candy B
Wakanda	Candy B	3399		T Shirt	T Shirt C	T Shirt D
Wakanda	T Shirt C	5393		Shoes	Shoes Y	Shoes Z
Wakanda	T Shirt D	508		Hat	Hat L	Hat M	Hat N
Wakanda	Shoes Y	642		Gloves	Gloves F	Gloves G	Gloves H
Wakanda	Shoes Z	10
Gotham	Candy A	194
Gotham	Candy B	2910
Gotham	T Shirt C	664
Gotham	T Shirt D	760		Store Performance	Category	Sold
Gotham	Shoes Y	0		Wakanda	Candy	?
Gotham	Shoes Z	0		Wakanda	T Shirt	?
Gotham	Hat L	83		Wakanda	Shoes	?
Gotham	Hat M	282		Wakanda	Hat	?
Gotham	Hat N	212		Wakanda	Gloves	?
Gotham	Gloves F	33440		Gotham	Candy	?
Gotham	Gloves G	185		Gotham	T Shirt	?
Gotham	Gloves H	22		Gotham	Shoes	?
Smallville	Candy A	501		Gotham	Hat	?
Smallville	Candy B	2289		Gotham	Gloves	?
Smallville	T Shirt C	701		Smallville	Candy	?
Smallville	T Shirt D	4941		Smallville	T Shirt	?
Smallville	Shoes Y	7		Smallville	Shoes	?
Smallville	Shoes Z	2245		Smallville	Hat	?
Smallville	Hat L	5960		Smallville	Gloves	?
Smallville	Hat M	1091

<tbody>
</tbody>

 

[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

Row\Col	H​	I​	J​	K​	L​
16​
17​
18​	Sum of Sold	Column Labels
19​	Row Labels	Gotham	Smallville	Wakanda	Grand Total
20​	Candy A	194​	501​	21295​	21990​
21​	Candy B	2910​	2289​	3399​	8598​
22​	Gloves F	33440​			33440​
23​	Gloves G	185​			185​
24​	Gloves H	22​			22​
25​	Hat L	83​	5960​		6043​
26​	Hat M	282​	1091​		1373​
27​	Hat N	212​			212​
28​	Shoes Y	0​	7​	642​	649​
29​	Shoes Z	0​	2245​	10​	2255​
30​	T Shirt C	664​	701​	5393​	6758​
31​	T Shirt D	760​	4941​	508​	6209​
32​	Grand Total	38752​	17735​	31247​	87734​
33​

 

[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>

Store Location	Item Name	Item Sold		Category	Item Name	Item Name	Item Name
Wakanda	Candy A	21295		Candy	Candy A	Candy B
Wakanda	Candy B	3399		T Shirt	T Shirt C	T Shirt D
Wakanda	T Shirt C	5393		Shoes	Shoes Y	Shoes Z
Wakanda	T Shirt D	508		Hat	Hat L	Hat M	Hat N
Wakanda	Shoes Y	642		Gloves	Gloves F	Gloves G	Gloves H
Wakanda	Shoes Z	10
Gotham	Candy A	194
Gotham	Candy B	2910
Gotham	T Shirt C	664
Gotham	T Shirt D	760		Store Performance	Category	Category Sold	Category Sold
Gotham	Shoes Y	0		Wakanda	Candy	3399	24694
Gotham	Shoes Z	0		Wakanda	T Shirt	508	5901
Gotham	Hat L	83		Wakanda	Shoes	10	652
Gotham	Hat M	282		Wakanda	Hat	0	0
Gotham	Hat N	212		Wakanda	Gloves	0	0
Gotham	Gloves F	33440		Gotham	Candy	2910	3104
Gotham	Gloves G	185		Gotham	T Shirt	760	1424
Gotham	Gloves H	22		Gotham	Shoes	0	0
Smallville	Candy A	501		Gotham	Hat	282	577
Smallville	Candy B	2289		Gotham	Gloves	185	33647
Smallville	T Shirt C	701		Smallville	Candy	2289	2790
Smallville	T Shirt D	4941		Smallville	T Shirt	4941	5642
Smallville	Shoes Y	7		Smallville	Shoes	2245	2252
Smallville	Shoes Z	2245		Smallville	Hat	1091	7051
Smallville	Hat L	5960		Smallville	Gloves	0	0
Smallville	Hat M	1091

<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>
</tbody>

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>

Store Performance	Category	Category Sold
Wakanda	Candy	?
Wakanda	T Shirt	?
Wakanda	Shoes	?
Wakanda	Hat	?
Wakanda	Gloves	?
Gotham	Candy	?
Gotham	T Shirt	?
Gotham	Shoes	?
Gotham	Hat	?
Gotham	Gloves	?
Smallville	Candy	?
Smallville	T Shirt	?
Smallville	Shoes	?
Smallville	Hat	?
Smallville	Gloves	?

<colgroup><col style="width: 118px"><col width="72"><col width="89"></colgroup><tbody>
</tbody>

<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

Row\Col	H​	I​	J​	K​	L​
16​
17​
18​	Sum of Sold	Column Labels
19​	Row Labels	Gotham	Smallville	Wakanda	Grand Total
20​	Candy A	194​	501​	21295​	21990​
21​	Candy B	2910​	2289​	3399​	8598​
22​	Gloves F	33440​			33440​
23​	Gloves G	185​			185​
24​	Gloves H	22​			22​
25​	Hat L	83​	5960​		6043​
26​	Hat M	282​	1091​		1373​
27​	Hat N	212​			212​
28​	Shoes Y	0​	7​	642​	649​
29​	Shoes Z	0​	2245​	10​	2255​
30​	T Shirt C	664​	701​	5393​	6758​
31​	T Shirt D	760​	4941​	508​	6209​
32​	Grand Total	38752​	17735​	31247​	87734​
33​

<tbody>
</tbody>

 

[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>

Store Performance	Category	Category Sold
Wakanda	Candy	3399
Wakanda	T Shirt	508
Wakanda	Shoes	10
Wakanda	Hat	0
Wakanda	Gloves	0
Gotham	Candy	2910
Gotham	T Shirt	760
Gotham	Shoes	0
Gotham	Hat	282
Gotham	Gloves	185
Smallville	Candy	2289
Smallville	T Shirt	4941
Smallville	Shoes	2245
Smallville	Hat	1091
Smallville	Gloves	0

<colgroup><col style="width: 118px"><col width="72"><col width="89"></colgroup><tbody>
</tbody>

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