HI Wilsone,
This may help, but I'm a bit in the dark here with no view of the Transaction's table and no idea of how the selected cell (D53) relates to the rest of the cells in row 53.
The main issue with your formula appears to be the prsenttion and construction of the Condition statements in SUMIFS.
Here's what the Formula Browser has to say about SUMIFS: (Notes in italics.)
The SUMIFS function returns the sum of the cells in a collection where the test values meet the given conditions.
SUMIFS(sum-values, test-values, condition, test-values…, condition…)
sum-values: A collection containing the values to be summed. sum-valuescan contain number values, date/time values, or duration values.
the collection of values in the "Amounts" column (of Table 1 on the Transactions sheet)
test-values: A collection containing values to be tested. test-values can contain any value.
the date and time values in column A of Table 1 of the Transactions sheet
condition: An expression that compares or tests values and results in the boolean value TRUE or FALSE.
> and a specific date (and time), presented as a text string. more on this below.
Condition can include comparison operators, constants, the ampersand concatenation operator, references, and wildcards. You can use wildcardsto match any single character or multiple characters in the expression. The wildcards you can use are ? (question mark), * (asterisk), and ~ (tilde). To find more information about wildcards, open the Help menu in your app and enter “wildcard” in the search field.
test-values…: Optionally include one or more additional collections containing values to be tested. Each test-values collection must be followed immediately by a condition expression.This pattern of test-values, conditioncan be repeated as many times as needed.
the same set of test values as above.
condition…: If an optional collection of test-values is included, an additional expression that results in a boolean value TRUE or FALSE. There must be one condition following each test-values collection; therefore, this function will always have an odd number of arguments.
a second comparison: < and a fixed date, eight days after the date in the first condition.
Notes
- For each of the test and condition value pairs, the corresponding (same position within the collection) cell or value is compared to the condition. If all of the conditions are met, the corresponding cell or value in sum-values is included in the sum.
- All collections must be of the same size.
Both these 'rules' are followed.
The sticking point in SUMIFS formulas is usually in expressing the conditions as a text string while leaving them able to adjust to their current location while the formula is applied to a series of rows. A good friend here is the concatenation operator.
Assuming the cells references are correct and the dates in column A of 'this table' match the manually entered dates in your first screen shots. this attempt is 'almost there':
Change ">A53" to ">"&A53
Change "<A51" to ">"&A51
The cell references A53 and A51 will be displayed as tokens, indicating they are active references that will return the value in that cell (a Date&Time value). That value will be concatenated with the comparison sign to form the text string required by SUMIFS.
Regards,
Barry
