sumifs with name range and lookup conditions

[MilindP]

mode	party	item	qty		books
Cash	Depot1	Book100pg	100		Book100pg
Cash	Depot1	Book200pg	25		Book200pg
Cash	Depot3	Book200pg	65		Book400pg
Cash	Depot1	PencilLight	150		Book800pg
Chq	Depot3	Book200pg	150
Chq	Depot2	Book400pg	75
Cash	Depot1	TextBookE	75
Chq	Depot3	TextBookB	35	
Chq	Depot2	PencilDark	144	=sumifs(qty,mode,"Cash",item,{"Book100pg","Book200pg"} gives answer 100+25+65 = 190 =sumifs(qty,mode,"Cash",item,books) = answer 0 any solution for this <tbody> </tbody>

<tbody>
</tbody><colgroup><col span="2"><col><col span="2"><col></colgroup>

 

[Aladin Akyurek]

Like this:

=SUMPRODUCT(SUMIFS(qty,mode,"cash",Item,books))

 

[MilindP]

thanks
it works
but getting answer 0 for multiple criteria

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​
1​	mode	party	item	qty		books
2​	Cash	Depot1	Book100pg	100​		Book100pg
3​	Cash	Depot1	Book200pg	25​		Book200pg
4​	Cash	Depot3	Book200pg	65​		Book400pg
5​	Cash	Depot1	PencilLight	150​		Book800pg
6​	Chq	Depot3	Book200pg	150​
7​	Chq	Depot2	Book400pg	75​
8​	Cash	Depot1	TextBookE	75​
9​	Chq	Depot3	TextBookB	35​	
10​
11​
12​				190​

<tbody>
</tbody>

d12=SUMPRODUCT(SUMIFS(D2:D9,A2:A9,A2,C2:C9,F2:F3))

 

[Aladin Akyurek]

thanks
it works
but getting answer 0 for multiple criteria

You probably did not define properly the range names the formula referes to...

See the workbook which implements the set up: Dropbox - MilindP sumifs with name range and lookup conditions.xlsx