Formule SOM.ALS geeft niet gewenst resultaat

[appel1982]

Goededag,

Hopelijk kan iemand mij helpen met het volgende.
Ik wil door middel van de formule som.als in het werkblad Controle een controle uitvoeren met de werkbladen Appels t/m Kiwi.
Het criterium wat ik hanteer voor de formule is het veld Grootboeksleutel.
De cellen H20 t/m H24 geven niet het resultaat wat ik verwacht.
Dit zou moeten zijn:
46.031.884,40
10.000,00
14.256,00
630.480,00
142.638,10

Ergens in het werkblad Controle of in het werkblad Peren gaat er iets niet goed.
Heel erg bedankt voor de hulp!

[EnerQi]

Grootboeksleutel 5 + 6 worden bij elkaar geteld omdat de sleutels aan elkaar gelijk zijn. Dit verklaard nog niet het bedrag in C20, maar lijkt mij een fout. Wist je al

Kun je niet even de formule zo maken dat ie alleen het tabblad "Peren" neemt? Zo sluit je uit dat er misschien dubbele waarde in andere tabbladen zit

[ Voor 35% gewijzigd door EnerQi op 30-03-2022 11:10 ]

[appel1982]

Bedankt voor je reactie.
grootboeksleutel 5 en 6 zijn inderdaad aan elkaar gelijk en worden opgeteld. Dat is ook de bedoeling.
De andere grootboeksleutels zijn echter anders en worden ook opgeteld.
De formule ziet de grootboeksleutels dus niet als uniek.
Is de grootboeksleutel te lang? Dien ik een andere formule te gebruiken?

[EnerQi]

Kun je ook checken of er misschien niet extra data staat in verdere rijen in Peren? Dit zou je snel moeten zien aan de scrolbalk aan de rechterkant. Deze is nu niet zichtbaar in je plaatje ;-).

Er is (of was voorheen?) een limiet van 7 "als" formules. Of dit nog geldt voor de huidige versie weet ik dus niet. Jouw formule heeft er maar 6 dus verwacht ik niet dat daar het probleem in zit.

[ Voor 34% gewijzigd door EnerQi op 30-03-2022 11:14 ]

[appel1982]

Nee, ik heb voor de zekerheid alle verdere rijen verwijderd, maar dat is het niet helaas.

[purge]

Wat als je het grootboeksleutel tussen dubbele aanhalingstekens zet, in de formule?

=SOM.ALS(bereik; "0002.0000...000"; optelbereik)

quote: https://support.microsoft...5c-4483-a712-1697a653039b

Belangrijk: Een tekstcriterium of een ander criterium dat logische of wiskundige symbolen bevat, moet tussen dubbele aanhalingstekens staan ("). Als het criterium numeriek is, zijn geen dubbele aanhalingstekens vereist.

[ Voor 5% gewijzigd door purge op 30-03-2022 11:33 ]

[appel1982]

Nee, helaas werkt dit ook niet. Nu is het resultaat 0 voor elke regel.

[appel1982]

Mag/kan ik het bestand op een of andere manier met jullie delen?
Dat werkt makkelijk dan met afbeeldingen.
Op een ander werkblad krijg ik met andere data namelijk wel het gewenste resultaat.
Zie onder. De bronnen van beide werkbladen zijn anders. Er zit wellicht iets niet goed in de celeigenschappen o.i.d.

[g0tanks]

Ik gok dat het te maken heeft met de lengte van de grootboeksleutel. Excel vindt het niet zo fijn om met hele lange strings te werken. Zie bijvoorbeeld: g0tanks in "Excel, aantal keuzes tellen"

[appel1982]

Helaas werken de genoemde oplossingen niet. Ik vind het vreemd dat in het laatste voorbeeld wat ik stuurde de lengte van de grootboeksleutel geen issue is. Daar geeft de formule som.als de verwachte waarden.

[Lustucru]

De formule in H20 is niet interessant. Wat is de formule in H21? Het zou me niks verbazen als daar als criterium ook c20 staat.

[ Voor 3% gewijzigd door Lustucru op 30-03-2022 13:46 ]

[appel1982]

Helaas niet:

[Lustucru]

Er is iets stuk, dat is wel duidelijk. qua troubleshooting zou ik de volgende volgorde uitvoeren
1 ram op f9 om een herberkening te forceren.
2 verwijder de lange criteriacellen in bron en controle en vervang ze door a,b,c,d,e
3 f9
4: Verwijder de som in kolom N van peren
5: f9
6: bouw het regel voor regel weer op.

[dixet]

Lustucru schreef op woensdag 30 maart 2022 @ 14:48:
Er is iets stuk, dat is wel duidelijk. qua troubleshooting zou ik de volgende volgorde uitvoeren
1 ram op f9 om een herberkening te forceren.
2 verwijder de lange criteriacellen in bron en controle en vervang ze door a,b,c,d,e
3 f9
4: Verwijder de som in kolom N van peren
5: f9
6: bouw het regel voor regel weer op.

Mijn geldt staat op 4:

Al blijft het dan wel raar dat hij de lege waarde in L8 hetzelfde vindt als de lange tekenreeks

[appel1982]

Het lukt nog steeds niet....:(

Is het mogelijk het bestand te uploaden/te delen zodat jullie mee kunnen kijken?
Ik zit hier nu al een paar dagen mee en ik denk niet dat ik er zelf uitkom.

[Lustucru]

appel1982 schreef op woensdag 30 maart 2022 @ 15:11:
Het lukt nog steeds niet....:(

Is het mogelijk het bestand te uploaden/te delen zodat jullie mee kunnen kijken?
Ik zit hier nu al een paar dagen mee en ik denk niet dat ik er zelf uitkom.

Het gaat mij er niet om wat niet lukt, maar wat het effect is als je bv de cellen c20..c24 verandert in a,b,c,d etc.

[appel1982]

Dan krijg ik de gewenste bedragen.
Maar het moet natuurlijk weer de grootboeksleutel worden en dan werkt het niet.

[ Voor 54% gewijzigd door appel1982 op 30-03-2022 17:27 ]

[Paultje3181]

Kolom D en Kolom G bevatten samen exact het bedrag wat er opgeteld wordt. De key is dus inderdaad te lang. Ik zou een hash toevoegen van de grootboek en die als key gebruiken.

code:

Public Function BASE64SHA1(ByVal sTextToHash As String)

    Dim asc As Object
    Dim enc As Object
    Dim TextToHash() As Byte
    Dim SharedSecretKey() As Byte
    Dim bytes() As Byte
    Const cutoff As Integer = 5

    Set asc = CreateObject("System.Text.UTF8Encoding")
    Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")

    TextToHash = asc.GetBytes_4(sTextToHash)
    SharedSecretKey = asc.GetBytes_4(sTextToHash)
    enc.Key = SharedSecretKey

    bytes = enc.ComputeHash_2((TextToHash))
    BASE64SHA1 = EncodeBase64(bytes)
    BASE64SHA1 = Left(BASE64SHA1, cutoff)

    Set asc = Nothing
    Set enc = Nothing

End Function

Private Function EncodeBase64(ByRef arrData() As Byte) As String

    Dim objXML As Object
    Dim objNode As Object

    Set objXML = CreateObject("MSXML2.DOMDocument")
    Set objNode = objXML.createElement("b64")

    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    EncodeBase64 = objNode.text

    Set objNode = Nothing
    Set objXML = Nothing

End Function

Customizing the hash length

the hash initially is a 28 characters long unicode string (case sensitive + special chars)
You customize the hash length with this line: Const cutoff As Integer = 5
4 digits hash = 36 collisions in 6895 lines = 0.5 % collision rate
5 digits hash = 0 collisions in 6895 lines = 0 % collision rate

Schaamteloos gejat van https://superuser.com/que...on-to-create-a-hash-value, maar mochten zij het antwoord ooit verwijderen is dit antwoord niet nutteloos.