Strange Excel behavior in some IF functions

Hi Daniel, thank you for reaching out!

My name is Rolyn, an Independent Advisor and a Microsoft Excel User like you. I would love to help you out with your concern.

There's a thing in excel where it only treats the first 15 digits as significant and anything after the 15th digit, excel would turn it to zero. If the cells are formatted as a text, like in your case, once you apply a mathematical equation like sumif, it will
turn those digits into number format, hence the incorrect returned values.

Perhaps you can try using this formula on your Column C and see if it works: =SUMPRODUCT(--($A:$A=$A2),$B:$B)

This article gives a pretty good explanation about this:
https://wmfexcel.com/2015/01/24/15-significant-...

(Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised
on the site before you decide to download and install it.)

Let me know if that worked out well for you. Feel free to ask me if you need further assistance.

Thanks,

Rolyn

Hallo Daniel,

wie beschrieben, produziert SUMMEWENN einen Fehler.

Du kannst aber alternative Formeln benutzen:

=SUMMENPRODUKT(($A$2:$A$20=A2)*$B$2:$B$20)

oder

=SUMME(WENN($A$2:$A$20=A2;$B$2:$B$20))

oder

=SUMME(WENN(IDENTISCH($A$2:$A$20;A2);$B$2:$B$20))

Noch einfacher geht es mit einer Pivot-Table. Diese erkennt die unterschiedlichen Kriterien.

Schau mal hier rein:

https://1drv.ms/x/s!AqMiGBK2qniTge8tXR3I7rSKSKjuOw?e=jcaVWA

Claus

Es soll jetzt in Excel die neue Formel " WENNS" geben!!!!

Ach ja, ich habe office 2016 professional und das aktuelle Update.

Gibt's auch in Excel 2016 für Office 365, sprich Du brauchst ein Abo dafür.

Naja, ist ja nicht weiter weltbewegend, macht man sich grad selber. ;-)

Andreas.

Function WENNS(Logiktest1, Wert_wenn_wahr1, ParamArray Args())

Dim i As Integer

If Logiktest1 Then

WENNS = Wert_wenn_wahr1

Else

For i = 0 To UBound(Args) Step 2

If Args(i) Then

WENNS = Args(i + 1)

Exit Function

End If

Next

End If

End Function

Further researching indicates that this behavior is by design and there is not any option to adjust this behavior currently. Also, I noticed some folks in the internet are complaining these two functionalities omission and is urging the return of
the both. I will forward this suggestion to the proper channel so that our product can take consideration of adjusting this behavior.

Hallo Zusammen,

gibt es inzwischen eine Lösung?

Gruß,

Thanks Doug for your answer - i already unchecked all Boxes in the Protected View with no success.

Strange behavior is, that for the first time Word opens the documents in the edit view, the second attempt results in the reading mode...