conditional sum by matching name and date

Copper Contributor

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. 



Bastian H.



3 Replies
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))

@Hans Vogelaar 

Oh my god it actually works, thank you so much for your help. 

You made my day.