Formule Excel a.d.h.v. verschillende variabelen

[znight]

Hallo allemaal,

Ik ben op zoek naar een formule voor het volgende:
Ik heb een tabblad met circa 100 - 200k transactieregels. Daarnaast heb ik een tabblad met verkoopprijzen.

Hierbij een simpel voorbeeld:

Tabblad met transactieregels:
Kolom A: Bal (Materiaal)
Kolom B: 27-03-24 (Leverdatum)
Kolom C: 5 (Aantal)

1 tabblad met verkoopprijzen:
Kolom A: Bal (Materiaal), Kolom B: 01-01-2023 (Begindatum),Kolom C: 31-12-2023 (Einddatum),Kolom D: €12,00 (Verkoopprijs)
Kolom A: Bal (Materiaal), Kolom B: 01-01-2024 (Begindatum),Kolom C: 31-12-2024 (Einddatum),Kolom D: €15,00 (Verkoopprijs)

Het doel is nu dat ik via een formule de juiste verkoopprijs in Kolom D op het transactieregels tabblad te zien krijg. Dit door de combinatie Materiaal + leverdatum te vergelijken met het andere tabblad. In dit geval zou de datum in 2024 zijn, dus zou het €15,00 moeten zijn.

Bovenstaand is een simpel voorbeeld met 1 artikel, maar in werkelijkheid zijn het er honderden, vandaar dat het een formule o.i.d. dient te zijn.

Iemand enig idee waarmee dit zou kunnen?

[Reinier]

Bijv. met sommen.als() met een aantal criteria.

[znight]

Reinier schreef op woensdag 27 maart 2024 @ 14:32:
Bijv. met sommen.als() met een aantal criteria.

Kan je in een sommen.als ook zeggen dat de datum dan bijv. groter moet zijn dan 01-01-2023 en kleiner dan 31-12-2023?
In principe lukt dat inderdaad met elke kolom, behalve dat ik niet weet hoe ik dat met die datums moet doen.

[dixet]

Jazeker kan dat, zie het voorbeeld in Sommen.als tot een bepaalde datum.

[znight]

Dankjewel. Helaas werkt dit niet in mijn situatie.

Probleem hiermee is dat er variabelen zijn in de verkoopprijzen en dat deze ook in een willekeurige volgorde staan. Ik zal het hieronder even proberen te schetsen:
Waar ik mee zit is dat hij in de rechterkolom moet gaan zoeken naar de juiste verkoopperiode. In je link is het een hard getal (bijv. 1-1-24) al kan dat hier verschillend zijn.

[ Voor 17% gewijzigd door znight op 28-03-2024 08:19 ]

[dixet]

In het eerste antwoord staat toch netjes een formule met celverwijzingen in plaats van een hard getal?

code:

=SOMMEN.ALS(C$1:C$9;A$1:A$9;A$1;B$1:B$9;">"&B9;B$1:B$9;"<"&B4)

Die is heel makkelijk om te bouwen naar jouw situatie, alleen de juiste celverwijzingen in de formule stoppen

[znight]

dixet schreef op donderdag 28 maart 2024 @ 08:40:
In het eerste antwoord staat toch netjes een formule met celverwijzingen in plaats van een hard getal?

code:

=SOMMEN.ALS(C$1:C$9;A$1:A$9;A$1;B$1:B$9;">"&B9;B$1:B$9;"<"&B4)

Die is heel makkelijk om te bouwen naar jouw situatie, alleen de juiste celverwijzingen in de formule stoppen

Allereerst bedankt voor het meedenken. Misschien begrijp ik het verkeerd, dus begrijp me niet verkeerd, maar is het niet zo dat als ik regel 1 met regel 1 vergelijk. (Dus de voetbal 27-01 met rechts regel 1, dan werkt het perfect.

Een beter voorbeeld is denk ik regel 6. Voetbal 28-8, welke de prijs van regel 2, €12,00 zou moeten zijn. Misschien is het hier mijn onkunde, maar welke cel verwijzing kan ik daar automatisch in zetten? Natuurlijk zou ik handmatig 01-04-24 als begindatum in kunnen vullen, maar dat wil ik niet doordat het een lijst is met 100k regels

[dixet]

Als ik er even van uitga dat in jouw screenshot de kolommen A t/m I staan zou je deze formule in cel D2 moeten zetten. Die kan je vervolgens doortrekken naar de onderliggende cellen (ik heb de engelstalige Excel, zelf even aanpassen naar de nederlandse formule)

code:

=SUMIFS(I:I;F:F;A2;G:G;"<="&B2;H:H;">="&B2)*C2

Wat deze zegt is: tel kolom I op wanneer de waarde in kolom F gelijk is aan de waarde in A2, de waarde in kolom G kleiner of gelijk aan de datum in B2 en de waarde in kolom H groter of gelijk aan de datum in C2. Vermenigvuldig dit gevonden bedrag met het aantal in C2

Je krijgt dit als resultaat:

[ Voor 3% gewijzigd door dixet op 28-03-2024 09:17 ]

[znight]

Top, het werkt. Dankjewel!! Weer wat geleerd.