Samenvatting
Als u per maand in kolommen wilt optellen, kunt u de SUMIFS-functie samen met de EOMONTH-functie gebruiken. In het getoonde voorbeeld is de formule in G5:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Deze formule gebruikt drie benoemde bereiken: "aantal" (D5: D15), "klant" (B5: B15) en "datum" (C5: C15).
Uitleg
De SUMIFS-functie is ontworpen om waarden in een bereik op te tellen op basis van een of meer criteria. In dit geval hebben we drie criteria nodig:
- Klantnaam = klant in kolom F
- Datum> = eerste van maand (vanaf datum in rij 4)
- Datum <= einde maand (vanaf datum in rij 4)
Kernpunt: de maandnamen in G4: I4 zijn feitelijk geldige datums, opgemaakt met de aangepaste getalnotatie "mmm". Hierdoor kunnen we de datumwaarden in G4: I4 rechtstreeks gebruiken voor criteria # 2 en # 3 hierboven.
Terug in SUMIFS wordt het eerste bereik / criterium-paar gebruikt om de klantnaam te controleren:
client,$F5
Merk op dat F5 een gemengde referentie is, met de kolom vergrendeld. Hierdoor kan de rij veranderen terwijl de formule door de tabel wordt gekopieerd, maar de clientnaam komt altijd uit kolom F.
Het tweede bereik / criterium-paar wordt gebruikt om datums te vergelijken met de eerste van de maand:
date,">="&G$4
Zoals hierboven vermeld, zijn de waarden in G4: I4 feitelijk datums: 1 januari 2019, 1 februari 2019 en 1 maart 2019. Dit criterium controleert dus eenvoudig op datums die groter zijn dan de datums van de eerste maand in rij 4. Merk op dat dit is weer een gemengde referentie, dit keer met de rij vergrendeld. Hierdoor kan de kolom veranderen terwijl de formule over de tabel wordt gekopieerd, maar blijft het rijnummer vast. De aaneenschakeling met een ampersand (&) is nodig bij het bouwen van criteria die een logische operator en een waarde uit een andere cel gebruiken.
Het derde bereik / criterium-paar wordt gebruikt om datums te vergelijken met de laatste dag van de maand:
date,"<="&EOMONTH(G$4,0)
Om de laatste dag van elke maand te krijgen, gebruiken we de EOMONTH-functie op de datum uit de kolomkop in rij 4. Met nul opgegeven voor het argument months, geeft EOMONTH de laatste dag van dezelfde maand als resultaat. Nogmaals, de verwijzing naar G4 is gemengd om te voorkomen dat de rij verandert.
Draaitafel oplossing
Een draaitabel zou een uitstekende oplossing zijn voor dit probleem, omdat deze automatisch op maand kan worden gegroepeerd zonder formules. Zie deze video voor een vergelijking van formules versus draaitabellen naast elkaar: Waarom draaitabellen.
