summation

[MilindP]

mode	party	item	qty	books	textbooks	depots
Cash	Depot1	Book100pg	100	Book100pg	TextBookB	Depot1
Cash	Depot1	Book200pg	25	Book200pg	TextBookC	Depot2
Cash	Depot3	Book200pg	65	Book400pg	TextBookE	Depot3
Cash	SHOP	PencilLight	150	Book800pg
Chq	SHOP	Book200pg	150
Chq	SHOP	TextBookE	75
Cash	SHOP	TextBookE	75
Chq	SHOP	PencilLight	35
Chq	SHOP	TextBookE	144

<tbody>
</tbody>

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