Forum Discussion
conditional sum by matching name and date
Hi, I´m trying to build a financial template for personal use and I seem to have walked into an issue regarding a SUMIFS- (German: SUMMEWENNS) formula. FYI JAHR(=YEAR)
=SUMMEWENNS(Tracking[Amount]; Tracking[Type]; type; JAHR(Tracking[effective_date]); selected_Jahr)
This should only add up values from the Tracking[Amount] column if the values in the respective Tracking[Type] column exactly match the value type. This calculation should take place depending on whether the year from the Tracking[tracked date] column exactly matches the year under the selected_year identifier. The value for selected_year is exchangeable depending on what year is selected.
The formula =SUMMEWENNS(Tracking[Amount]; Tracking[Type]; type) already returns the right numbers but due to the missing statement incustomizable in year and period.
Now for some reason, as soon as I add the other conditional statement, there is a window showing up saying that there is a problem with the formula even though I only get valid values for each condition.
The Syntax should be correct and I feel like I've already tried everything possible. I tried changing the Format and switching formulas, but nothing worked.
It would be a huge help if someone could help me with this.
Greetings,
Bastian H.
- Note: in specific Excel Versions statements are grouped by ; instead of ,
The 2nd, 4th etc. arguments of SUMMEWENNS must be ranges. They cannot be expressions such as JAHR(Tracking[effective_date]).
Try this:
=SUMMENPRODUKT(Tracking[Amount]; (Tracking[Type]=type)*(JAHR(Tracking[effective_date])=selected_Jahr))
