# conditional sum by matching name and date

Copper Contributor

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

3 Replies

# Re: conditional sum by matching name and date

Note: in specific Excel Versions statements are grouped by ; instead of ,

# Re: conditional sum by matching name and date

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

# Re: conditional sum by matching name and date

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

You made my day.