Als functie gebruiken bij datum

[sophia491]

Een collega van mji heeft eveneens een klein winkeltje en houdt in Excel bij wat er in- en verkocht wordt.
Nu wil hij graag op een totaalblad per maand de in- en verkoop optellen.
Op het blad waar de in- en verkoopdatum staan van de artikelen leek het mij handig om een alsfunctie te gebruiken voor bijvoorbeeld de dagen tussen 31 december 2002 en 1 februari 2003. Ik heb de celeigenschappen voor de kolommen met de in- en verkoopdatum op jjjjmmdd gezet in plaats van de standaard datumnotatie.
Bij mijn formule wordt in de kolom met de formule het bedrag weergegeven uit de kolom inkoop. Echter, bij de dagen na 31 januari 2003 wordt ook het bedrag weergegeven, terwijl ik daar juist niets wil zien. Wat moet ik veranderen in mijn formule?
Mijn opzet is om op het totaalblad de som uit de kolommen inkoopjan, verkoopjan enzovoorts weer te geven.

Ik heb het voorbeeldbestandje bijgevoegd

Alvast bedankt voor de hulp

Sophia

 

[jzeedijk]

Hoi Sophia,

Je kunt beter gebruik maken van de functie somproduct.
Ik heb even een kolom ingevoegd tussen verkoopprijs en datum verkoop (kolom F), de kolommen erna schuiven dus 1 op.

TOTAAL!B5: =SOMPRODUCT((MAAND('1aardewerk'!$H$3:$H$999)=RIJEN(B$5:B5))*('1aardewerk'!$I$3:$I$999))
En naar beneden kopieren.

TOTAAL!B24: =SOMPRODUCT((MAAND('1aardewerk'!$D$3:$D$999)=RIJEN(B$24:B24))*('1aardewerk'!$E$3:$E$999))
En naar beneden kopieren.

En voor de verliezen in de betreffende maand:
TOTAAL!B43: =SOMPRODUCT((MAAND('1aardewerk'!$G$3:$G$999)=Rijen(B$43:B43)*('1aardewerk'!$F$3:$F$999))-SOMPRODUCT((ISLEEG('1aardewerk'!$G$3:$G$999))*('1aardewerk'!$F$3:$F$999))

TOTAAL!B44: =SOMPRODUCT((MAAND('1aardewerk'!$G$3:$G$999)=Rijen(B$43:B44)*('1aardewerk'!$F$3:$F$999))
En naar beneden kopieren.

Indien de verkoopprijs lager lager is dan geprijsd:
TOTAAL!B62: =SOMPRODUCT((MAAND($H$3:$H$999)=RIJEN(N$3:N3))*(($I$3:$I$999)-($F$3:$F$999)))+SOMPRODUCT((ISLEEG($H$3:$H$999))*($F$3:$F$999))

TOTAAL!B63: =SOMPRODUCT((MAAND($H$3:$H$999)=RIJEN(N$3:N3))*(($I$3:$I$999)-($F$3:$F$999)))
En naar beneden kopieren.

Wat doet de formule somproduct in B5:
In het bereik H3:H999 staan de verkoopdatums van de producten. Als de maand daarvan gelijk is aan Rijen(B$5:B5) (dat is 1 rij, komt dus overeen met maand 1, is januari) komt uit de vergelijking Waar en anders Onwaar. In een vermenigvuldiging 1 en anders 0. Dat wordt vermenigvuldigd met de verkoopprijs in het bereik I3:I999. Als een product in jauari wordt verkocht (Waar of 1) wordt dit vermenigvuldigd met de verkoopprijs, als het product in augustus wordt verkocht (Onwaar of 0) dan wordt dit vermenigvuldigd met de verkoopprijs (0*de verkoopprijs =0). Vervolgens wordt hier de som van bepaald.

Er is 1 maar aan dit verhaal. Tussen getallen mag geen tekst staan, dus Gebroken 27-1-03 moet in een andere kolom komen anders volgt er een foutmelding. Dat bracht mij op het idee om een kolom F in te voegen waarin de verliesdatum wordt genoteerd. Aangezien de maand van een lege cel overeenkomt met 1 wordt ook de verkoopprijs van alle niet verloren producten bij januari opgeteld. Dat willen we niet dus moet hiervoor gecorrigeerd worden. Datzelfde geldt natuurlijk ook voor de korting die gegeven wordt (prijs verkoop lager dan prijs geprijsd)

Ik denk dat je er nog niet bent, maar meer info nu lijkt mij te veel van het goede.

Jeroen

 

[sophia491]

somproduct

Dag Jeroen,

Hartelijk dank voor je formule met somproduct.
Ik heb het toegepast bij de kolommen voor inkomsten en uitgaven.

Ik loop echter vast bij de verliezen. Ik heb maar weer een bestandje toegevoegd van wat ik gedaan heb.
Wanneer ik in cel B43 van het totaalblad de formule invoer zoals jij hebt aangegeven, krijg ik een foutmelding. Als ik de correctie aanvaard zoals Excel die aangeeft, krijg ik een zogenaamde WAARDE melding.
Cel B43 is bij mij de maand januari. Misschien had jij het anders gedacht? De volgende formule voor cel B44 en verder naar beneden werkt wel.

De volgende formules weet ik niet te plaatsen.
Moeten hier weer rijen van de maanden onder?

Ik vraag wel heel veel, maar vind het ook ontzettend leuk om te leren.
Ik hoop dat je nog wat tijd kunt vinden voor mij.
Alvast bedankt,
Sophia

 

[jzeedijk]

Hoi Sophia,

Er zaten een paar type-foutjes in je formule:
De goede:
=SOMPRODUCT((MAAND('1aardewerk'!$G$3:$G$999)=RIJEN(B$43:B43))*('1aardewerk'!$F$3:$F$999))-SOMPRODUCT((ISLEEG('1aardewerk'!$G$3:$G$999))*('1aardewerk'!$F$3:$F$999))

Ik dacht even vooruit, stel een vriendin van mij komt langs bij tante Bets (ze houd erg van curiosa), maar ze weigert de verkoopprijs te betalen, ze dingt altijd af. Ook tante Bets moet brood op de plank hebben dus dat prulletje van 8 euro gaat weg voor 6 euro. De winst blijft natuurlijk het verschil tussen inkoop en verkoop, maar nu wordt duidelijk in welke maand er vaker korting gegeven wordt op de officiele verkoopprijs. Bovendien kan je zien of het geven van korting van invloed is op je omzet of je winst.

Inderdaad ook deze moet weer onderaan toegevoegd worden.

Voor wat betreft vragen, meestal kan ik hier wel tijd voor vinden. Een andere mogelijkheid, gebruik de zoekfunctie in dit forum, vul somproduct (of iets anders) in en zoek binnen Excel. Vaak vind je oplossingen voor problemen die je ervoor nooit opgelost kreeg.

Als ik trouwens iets mag opmerken, je spreadsheet ziet er erg overzichtelijk uit. :thumb:

Jeroen

 

[jpvs]

Hier nog een bestand dat je misschien kunt gebruiken?

Pierre

 

[jpvs]

Oeps?

 

[sophia491]

Bedankt

Ik wil iedereen en met name Jeroen bedanken voor de hulp. Ik ben er nog niet helemaal uit, maar heb de eigenaar van de winkel aangeraden zich te registreren bij helpmij en zelf verder vragen te stellen.

Groet, Sophia

:thumb:

 

[tante Bets]

Bedankt Sophia en Jeroen voor de reacties. Hier kan ik echt verder mee.
Ik heb inderdaad nog een paar vraagjes. Omdat alle artikelen een nummer hebben loop ik vast bij de totaal berekening. Een artikel kan gekocht zijn in 2003 en verkocht in 2004. Als ik dus het totaal wil berekenen moet ik de totalen per jaar kunnen berekenen. Hoe moet ik de formule dan aanpassen?

Ook met het kopieren van de data uit het originele bestand heb ik problemen. Ook als ik de celeigenschappen aanpas (ik gebruik daarvoor de listmanager) dan lukt dat niet.

Ik ben nog aan het stoeien, ik hoop dat iemand een idee heeft.

Groeten tante Bets

 

[jzeedijk]

Goedemiddag tante,

Je kunt de somproduct-formule ook gebruiken in combinatie met jaar:
TOTAAL!B5: =SOMPRODUCT((JAAR('1aardewerk'!$H$3:$H$999)=2003)*('1aardewerk'!$I$3:$I$999))


Jeroen

 

[tante Bets]

hallo Jeroen,
ik heb geprobeerd wat gegevens in te voeren uit het originele bestand, maar dat liep al snel uit de hand.
Het probleem met het kopieren van de datums is wel gelukt, maar het omzetten naar de totalen uitgesplitst in 2003 en 2004 werd niets.
Het idee van jou om een extra kolom verliezen in te voeren is voor ons praktisch niet zo interessant, dus die heb ik verwijderd....
Ik zag ook dat er ineens alle rare toevoegingen in de formule stonden.
Ben ik op de goede weg of pak ik het verkeerd aan?
Groeten tante.

Het bestandje voeg ik toe

 

[tante Bets]

het bestand is te groot, ook in zip vorm, ik weet even niet wat te doen
tante

 

[jzeedijk]

Hoi tante,

Mail het filetje maar even naar jzeedijk#freeler.nl

Jeroen

 

[tante Bets]

probleem opgelost

Beste Jeroen, jouw oplossing was perfect, na wat gehussel met het overzetten vanuit het originele bestand, werken de koppelingen goed. Ik heb de formule hieronder vermeld.
Nogmaals bedankt.


=SUMPRODUCT((YEAR(INDIRECT("'"&B$3&"'!$G$3:$G$999"))=$B$1)*(MONTH(INDIRECT("'"&B$3&"'!$G$3:$G$999"))=ROWS(C$5:C5))*(INDIRECT("'"&B$3&"'!$H$3:$H$999")))

:thumb: :thumb: