Search code examples
google-sheetsgoogle-sheets-formula

Collect and Sum from same ID in google sheet

I am an amateur on Google sheet. I need help to sum up data with the same multiple ID. I manage to make dropdown calculation, but somehow its too much work. I want it summarized for me.. Can anyone help me? enter image description here I try to use sumifs

=SUMIFS(C4:C30;E4:E30;L4;D4:D30;K4)

Works OK, but then I have to select data I need all the time. I want it summarized , I was thinking to use query , but I found it difficult, and fail all the time...

Solution

  • Preface

    So the initial formula is correct:

    =SUMIFS(C2:C;D2:D;$K2;E2:E;$L2)

    This will sum all "belopp" for each pair of "konto" and "ID".

    As described, this formula uses the columns K ("konto") and L ("ID") for the look-ups, which can be tedious since K2 and L2 is changed manually via drop-downs.

    Solution

    One solution, is to put in a formula in the cell K2:

    =UNIQUE(D2:E)

    This will extract all unique combinations of "konto" and "ID", and populate the K and L columns. Now, the =SUMIFS(...)-formula can be extended for the entire J-column.

    This should yield the sum of "belopp" for all unique combinations of "konto" and "ID". Like so: enter image description here

    To re-iterate

    The J2-cell has the =SUMIFS(C2:C;D2:D;$K2;E2:E;$L2)-formula. Drag/Copy it for the entire J-column.

    The K2-cell has the formula: =UNIQUE(D2:E).