summation

[MilindP]

[TABLE="width: 836"]
<tbody>[TR]
[TD="width: 76"]mode[/TD]
[TD="width: 64"]party[/TD]
[TD="width: 79"]item[/TD]
[TD="width: 64"]qty[/TD]
[TD="width: 77"]books[/TD]
[TD="width: 79"]textbooks[/TD]
[TD="width: 78"]depots[/TD]
[/TR]
[TR]
[TD]Cash[/TD]
[TD]Depot1[/TD]
[TD]Book100pg[/TD]
[TD]100[/TD]
[TD]Book100pg[/TD]
[TD]TextBookB[/TD]
[TD]Depot1[/TD]
[/TR]
[TR]
[TD]Cash[/TD]
[TD]Depot1[/TD]
[TD]Book200pg[/TD]
[TD]25[/TD]
[TD]Book200pg[/TD]
[TD]TextBookC[/TD]
[TD]Depot2[/TD]
[/TR]
[TR]
[TD]Cash[/TD]
[TD]Depot3[/TD]
[TD]Book200pg[/TD]
[TD]65[/TD]
[TD]Book400pg[/TD]
[TD]TextBookE[/TD]
[TD]Depot3[/TD]
[/TR]
[TR]
[TD]Cash[/TD]
[TD]SHOP[/TD]
[TD]PencilLight[/TD]
[TD]150[/TD]
[TD]Book800pg[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Chq[/TD]
[TD]SHOP[/TD]
[TD]Book200pg[/TD]
[TD]150[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Chq[/TD]
[TD]SHOP[/TD]
[TD]TextBookE[/TD]
[TD]75[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Cash[/TD]
[TD]SHOP[/TD]
[TD]TextBookE[/TD]
[TD]75[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Chq[/TD]
[TD]SHOP[/TD]
[TD]PencilLight[/TD]
[TD]35[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Chq[/TD]
[TD]SHOP[/TD]
[TD]TextBookE[/TD]
[TD]144[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
SUMPRODUCT(SUMIFS(qty,mode,"Cash",item,books)) the answer is 190
but is one more criteria is added to this ---
SUMPRODUCT(SUMIFS(qty,mode,"Cash",item,books,party,depots)) the answer is 100 which is incorrect

 

[FormR]

Hi, you won't be able to use SUMIFS() to do that, you could try this alternative instead:

=SUMPRODUCT(--(mode="Cash"),--ISNUMBER(MATCH(item,books,0)),--ISNUMBER(MATCH(party,depots,0)),qty)

 

[AlanY]

your first formula should has answer of 125, the first 2 lines matched, and the second answer of 100 is correct

try

=SUMPRODUCT(qty,(mode="cash")*(item=books))

and

=SUMPRODUCT(qty,(mode="cash")*(item=books)*(party=depots))

 

[steve the fish]

Im not sure if this always works but so far it seems to:

=SUMPRODUCT(SUMIFS(qty,mode,"Cash",item,books,party,TRANSPOSE(depots)))

 

[FormR]

so far it seems to:

Ah, of course!

To the OP - that is the limit though, anymore "in list" type criteria will require a switch away from SUMIF()

 

[MilindP]

Thanks