Excel heeft al functies zoals de SUMIF en de SUMIFS voor het sommeren van gegevens per groep. Deze werken echter niet als u datum-tijdwaarden hebt gecombineerd. Dit artikel laat zien hoe u in Excel kunt optellen op datum zonder tijd met behulp van de functies SUMPRODUCT en INT.
Syntax
=SUMPRODUCT( bereik van waarden * ( INT( bereik van datum-tijd ) = cel van datum )
Stappen
- Begin met =SUMPRODUCT( functie
- Selecteer het bereik dat waarden bevat $C$3:$C$16
- Gebruik een sterretje om de waarden van het productbereik met voorwaardenbereik en open een haakje *(
- Vervolg met INT($B$3:$B$16) om het tijdgedeelte te verwijderen
- Voeg voorwaarde in met een gelijkheidsteken en criteriabereik =E3
- Type )) om de functie te sluiten en de formule te voltooien
Hoe
Excel bewaart datum- en tijdwaarden als getallen. Excel gaat ervan uit dat de geschiedenis begint op 1 jan 1900 en accepteert deze datum als 1. Terwijl hele getallen dagen vertegenwoordigen, vertegenwoordigt het decimaal de tijd. Bijvoorbeeld; 1/1/2018 is gelijk aan 43101, 12:00 is gelijk aan 0.5.
We kunnen de INT-functie gebruiken om ons te ontdoen van de tijdwaarden of het decimale deel van getallen. De INT-functie geeft eenvoudig het gehele getaldeel van een numerieke waarde terug. In ons geval geeft
INT($B$3:$B$16) {43292;43292;43292;43292;43292;43293;43293;43293;43293;43293;43293;43293;43294;43294}
Maar om de INT-functie op een matrix te laten werken, moeten we de SUMPRODUCT-functie gebruiken. Het vermogen van de SUMPRODUCT-functie om met matrices om te gaan zonder matrixformules te gebruiken, biedt een voordeel ten opzichte van de beperkingen van de SUMIF- en SUMIFS-functies. De SUMPRODUCT kan matrices van bereik – waardevoorwaarden evalueren en retourneren. We gebruiken deze functionaliteit om bereik – criteria paren op te lossen.
(INT($B$3:$B$16)=E3) retourneert {TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
Ook telt de SUMPRODUCT-functie de waarden in zijn argumentenarray op als er slechts één argument is. Voorwaardelijke vergelijkingen geven echter Booleaanse waarden terug. Ze moeten dus worden omgezet in getallen die betekenisvol zijn voor de SOMPRODUCT-functie. Gelukkig doet de productoperator (*) dit.
Door een waarde-array te vermenigvuldigen met een voorwaarden-array, krijgen we de arraywaarden die volgens onze behoeften worden gegenereerd.
$C$3:$C$16*(INT($B$3:$B$16)=E3) levert {1;2;3;4;0;0;0;0;0;0;0;0;0}
Tot slot levert de SUMPRODUCT de resultaten als som op datum in Excel, wat is waar we naar op zoek zijn.
=SUMPRODUCT($C$3:$C$16*(INT($B$3:$B$16)=E3)) levert 10 op voor de datum 7/11/2018.
Alternatieve optie
Een alternatieve manier is het gebruik van PivotTable die u meer opties kan geven dan het optellen van waarden. U vindt gedetailleerde informatie in Hoe groepeer je dagen zonder tijd met behulp van Pivot Table.
Zie ook gerelateerde artikelen hoe u gewogen gemiddelde berekent met SUMPRODUCT, hoe u 2d bereiken sommeert met SUMPRODUCT, en hoe u waarden sommeert met OR-operator met behulp van SUMPRODUCT met meerdere criteria.