Why is that when I call sum function over a defined name, the result is wrong?

[toaruScar]

I'll first reproduce my problem here:

I have 3 tables:

The upper left one contains the raw score (RawScore), the upper right one contains some modifications we want to apply to the scores (ScoreMod). And the lower one is the total score (Total).

I calculated the Tom's "effective scores" for each day by adding the modifiers to the raw scores with the following formula:

=LET(
MyDate,FILTER(RawScore[Date],RawScore[Name]=Total[@Name]),
MyRawScore,FILTER(RawScore[Score],RawScore[Name]=Total[@Name]),
MyModifier, XLOOKUP(MyDate, FILTER(ScoreMod[Date], ScoreMod[Name]=Total[@Name],0),  FILTER(ScoreMod[Modifier], ScoreMod[Name]=Total[@Name],0), 0),
MyModifier+MyRawScore)

When I put this formula at D12, and it correctly calculates the effective scores.

Then I define a name with the above function, called it "EffectiveScore"
The problem is that when I try to calculate the Tom's total score, by having

=SUM(EffectiveScore)

At B12, the amount is not correct, it shows 500, instead of 480.

But if I replace the formula with

=SUM(MAP(EffectiveScore, LAMBDA(x, INT(x))))

,
It returns the correct result, 480.

Why do I have to wrap the formula with bunch of useless extra functions to get the right result?

This sample worksheet is available at Sum is incorrect.xlsx

 

[bebo021999]

I don't have Excel 365, so unfortunately, I can't try it out myself. But, why not give SUMIF a shot?

=SUMIF(RawScore[Name],[@Name],RawScore[Score])+SUMIF(ScoreMod[Name],[@Name],ScoreMod[Modifier])

 

[toaruScar]

I don't have Excel 365, so unfortunately, I can't try it out myself. But, why not give SUMIF a shot?

=SUMIF(RawScore[Name],[@Name],RawScore[Score])+SUMIF(ScoreMod[Name],[@Name],ScoreMod[Modifier])

Thanks for the quick reply, but the sum here is just a quick way to demonstrate the problem I'm having. I'd like to be able to use the "EffectiveScore" name at more places and also in more complicated use cases to make my actual worksheet easier to understand. This wired behavior caught me off guard. I want to understand what's going on here, so there'll be no more surprises in the future.

 

[Fluff]

Not sure what is going on, but you can use

=SUM(+EffectiveScore)

 

[toaruScar]

Not sure what is going on, but you can use

=SUM(+EffectiveScore)

Wow, It works, but how? I don't even know one can use plus sign like this. I tried to find some documentation for this but can't find any, could you please explain what this plus sign does?

 

[Fluff]

It's often used to turn a range into an array, but as your formula returns an array I'm not sure why it makes a difference.

 

[toaruScar]

It's often used to turn a range into an array, but as your formula returns an array I'm not sure why it makes a difference.

Thanks a lot!

 

[Fluff]

You're welcome.