0

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!

1
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Mar 22, 2023 at 21:16

1 Answer 1

0

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().

5
  • Woo hoo! Thanks doubleunary! That worked great.
    – Ben
    Mar 23, 2023 at 13:47
  • In the results of the first cell it returns "sum max " before the number it returns. Is there any way to remove this text from the results?
    – Ben
    Mar 23, 2023 at 14:06
  • Edited the answer to add a label clause. Mar 23, 2023 at 14:54
  • I tried the new code and it didn't work quite right. I added that and it just overrode the first value and put the label "volume" in that cell. The problem I'm seeing, with the original code, is the first cell of the query returns "sum max 2798" in row 1 of column b. The following results just list the summed up volumes without the "sum max" proceeding it. Having the text stuck in the cell is making it hard for me to plug the data in the rest of the spread sheet.
    – Ben
    Mar 23, 2023 at 18:39
  • If you do not want headers to show, use a headers parameter of 0 and a label clause with ''. See query(). Mar 23, 2023 at 19:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.