google-sheetssumgoogle-sheets-formulasumifs

How do I use SUMIFS in google sheets to sum a column based on referenced criteria (ex: "<="$K$5)?


I have a list of downloaded transactions that each correspond to a specific account code? I want to sum the amount (column G) based on the following criteria: segment (column A), account (column C), and transaction period, which is a number 1-12 (column F). Cell reference K1 is where the current period is located on the sheet.G3 references the segment.screenshot of data

The formula I have so far is:

=SUMIFS( 'Data'!G:G,
  'Data'!A:A, $G$3,
  'Data'!F:F, "<="$K$1,
  'Data'!C:C, A27
)

If I remove the criteria for column F, it gives me a total but it is incorrect and I cannot tell how it is summing the data. Of course I want to keep that criteria in but I'm not sure how to make it work.

Edit. Below is the sample data as markdown table. Columns B, D, E omitted since they weren't in the original screencap.

A C F G
1 Segment Acct Per Amount
2 324091 53310 10 2,838,256.48
3 324091 53320 10 1,585,083.76
4 224091 53310 2 976,398.12
5 224091 53310 3 864,933.05
6 370496 52400 3 822,272.70
7 370496-00040 52400 5 822,272.70
8 370496-00040 52400 8 822,272.70
9 224091 53310 4 733.873.71

Solution

  • The formula is using an incorrect syntax. Add a concatenation operator & in the second criterion:

    =sumifs(
      'Data'!G:G,
      'Data'!A:A, $G$3,
      'Data'!F:F, "<=" & $K$1,
      'Data'!C:C, A27
    )
    

    See concat() a.k.a. &.