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 |
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. &
.