SUMIF for unique entries

Question

I need to do a SUMIFS statement in Google Sheets but am having a hard time not double counting duplicates in the data. For example:

Volume	Flavor	Batch #
6000	CLM	BT1000
6500	CLM	BT1000
7000	WBG	BT2000
2000	CLM	BT3000
3000	ISM	BT4000
5000	CLM	BT5000

I want to SUMIF the volume of all of matching flavors but only add the volume of each Batch # one time. Ideally this would count which ever volume is highest for the associated Batch # So in this instance if did a SUMIF for 'CLM' I would like to get 13500 as the answer.

I tried different variations with QUERY nested inside a SUM statement as well, but couldn't make it work. Open to any approach including using a helper column.

Thanks for the help!

Answer 1

Use query() with a group by clause, like this:

=query( 
  query( 
    A1:C, 
    "select B, C, max(A) 
     where B is not null 
     group by B, C", 1 
  ), 
  "select Col1, sum(Col3) 
   group by Col1 
   label sum(Col3) 'Volume' ", 1 
)

See query().