sumifs with name range and lookup conditions

[MilindP]

[TABLE="width: 412"]
<tbody>[TR]
[TD]mode[/TD]
[TD]party[/TD]
[TD]item[/TD]
[TD]qty[/TD]
[TD][/TD]
[TD]books[/TD]
[/TR]
[TR]
[TD]Cash[/TD]
[TD]Depot1 [/TD]
[TD]Book100pg[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD]Book100pg[/TD]
[/TR]
[TR]
[TD]Cash[/TD]
[TD]Depot1[/TD]
[TD]Book200pg[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD]Book200pg[/TD]
[/TR]
[TR]
[TD]Cash[/TD]
[TD]Depot3[/TD]
[TD]Book200pg[/TD]
[TD="align: right"]65[/TD]
[TD][/TD]
[TD]Book400pg[/TD]
[/TR]
[TR]
[TD]Cash[/TD]
[TD]Depot1[/TD]
[TD]PencilLight[/TD]
[TD="align: right"]150[/TD]
[TD][/TD]
[TD]Book800pg[/TD]
[/TR]
[TR]
[TD]Chq[/TD]
[TD]Depot3[/TD]
[TD]Book200pg[/TD]
[TD="align: right"]150[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Chq[/TD]
[TD]Depot2[/TD]
[TD]Book400pg[/TD]
[TD="align: right"]75[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cash[/TD]
[TD]Depot1[/TD]
[TD]TextBookE[/TD]
[TD="align: right"]75[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chq[/TD]
[TD]Depot3[/TD]
[TD]TextBookB[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chq






[/TD]
[TD]Depot2






[/TD]
[TD]
PencilDark







[/TD]
[TD="align: right"]144






[/TD]
[TD]



[TABLE="width: 412"]
<tbody>[TR]
[TD="align: right"]=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

[/TD]
[TD]









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






[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col span="2"><col><col span="2"><col></colgroup>[/TABLE]

 

[Aladin Akyurek]

Like this:

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

 

[MilindP]

thanks
it works
but getting answer 0 for multiple criteria

 

[MARZIOTULLIO]

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

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

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

1​

[/TD]
[TD]mode[/TD]
[TD]party[/TD]
[TD]item[/TD]
[TD]qty[/TD]
[TD][/TD]
[TD]books[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Cash[/TD]
[TD]Depot1[/TD]
[TD]Book100pg[/TD]
[TD]

100​

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

3​

[/TD]
[TD]Cash[/TD]
[TD]Depot1[/TD]
[TD]Book200pg[/TD]
[TD]

25​

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

4​

[/TD]
[TD]Cash[/TD]
[TD]Depot3[/TD]
[TD]Book200pg[/TD]
[TD]

65​

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

5​

[/TD]
[TD]Cash[/TD]
[TD]Depot1[/TD]
[TD]PencilLight[/TD]
[TD]

150​

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

6​

[/TD]
[TD]Chq[/TD]
[TD]Depot3[/TD]
[TD]Book200pg[/TD]
[TD]

150​

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

7​

[/TD]
[TD]Chq[/TD]
[TD]Depot2[/TD]
[TD]Book400pg[/TD]
[TD]

75​

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

8​

[/TD]
[TD]Cash[/TD]
[TD]Depot1[/TD]
[TD]TextBookE[/TD]
[TD]

75​

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

9​

[/TD]
[TD]Chq[/TD]
[TD]Depot3[/TD]
[TD]TextBookB[/TD]
[TD]

35​

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

10​

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

11​

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

12​

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

190​

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

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