1
The following code will give you the exact output you are looking for in your bottom example:
=arrayformula(if(
query(query({A:A,C:C*1,D:D},"select Col1,sum(Col2) where Col1 <> '' group by Col1 PIVOT Col3",1),"select Col1,Col2,Col3 order by Col2 desc, Col3 desc")="",0,
query(query({A:A,C:C*1,D:D},"select Col1,sum(Col2) where Col1 <> '...
Only top voted, non community-wiki answers of a minimum length are eligible
