Excel har redan funktioner som SUMIF och SUMIFS för att summera data efter grupper. Men; de kan inte fungera om du har datum- och tidsvärden kombinerade. Den här artikeln visar hur du summerar efter datum i Excel utan tid med hjälp av funktionerna SUMPRODUCT och INT.
Syntax
=SUMPRODUCT( intervall av värden * ( INT( intervall av datum-tid ) = cell av datum ) )
Steg
- Start med =SUMPRODUCT( funktion
- Typ av välj det intervall som innehåller värden $C$3:$C$16
- Använd asterisk för att produkt intervall värden med villkor intervall och öppna en parentes *(
- Fortsätt med INT($B$3:$B$16) för att ta bort tidsdelen
- Inför villkor med ett likhetstecken och kriterieområde =E3
- Typ )) för att stänga funktionen och avsluta formeln
Hur
Excel håller datum- och tidsvärden som siffror. Excel antar att historiken börjar från den 1 januari 1900 och accepterar detta datum som 1. Medan hela tal representerar dagar representerar decimaler tid. Till exempel; 1/1/2018 är lika med 43101, 12:00 är lika med 0,5.
Vi kan använda INT-funktionen för att bli av med tidsvärdena eller decimaldelen av talen. INT-funktionen returnerar helt enkelt hela taldelen från ett numeriskt värde. I vårt fall;
INT($B$3:$B$16) returnerar {43292;43292;43292;43292;43292;43293;43293;43293;43293;43293;43293;43293;43293;43293;43293;43293;43294;43294}
Hursomhelst; för att få INT-funktionen att fungera på en matris, måste vi använda funktionen SUMPRODUCT. SUMPRODUCT-funktionens förmåga att hantera matriser utan att använda matrisformler ger en fördel jämfört med begränsningarna hos funktionerna SUMIF och SUMIFS. SUMPRODUCT kan utvärdera och återge matriser från villkor för intervall – värde. Vi använder denna funktionalitet för att lösa kriterierna intervall – kriterier par.
(INT($B$3:$B$16)=E3) returnerar {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
Den SUMPRODUCT-funktionen summerar också värdena i sin argumentmatris om det bara finns ett argument. Villkorliga ekvationer returnerar dock boolska värden. De måste därför omvandlas till tal som är meningsfulla för SUMPRODUCT-funktionen. Lyckligtvis gör produktoperatorn (*) detta.
Genom att multiplicera en värdearray med en villkorarray får vi arrayvärden som genereras enligt våra behov.
$C$3:$C$16*(INT($B$3:$B$16)=E3) returnerar {1;2;3;4;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
Slutligt returnerar SUMPRODUCT resultaten som summa efter datum i Excel vilket är vad vi är ute efter.
=SUMPRODUCT($C$3:$C$16*(INT($B$3:$B$16)=E3)) returnerar 10 för datumet 7/11/2018.
Alternativt alternativ
Ett alternativt sätt är att använda PivotTable som kan ge dig fler alternativ än att summera värden. Du hittar detaljerad information i Hur man grupperar dagar utan tid med Pivot Table.
Se även relaterade artiklar om hur man beräknar vägt genomsnitt med SUMPRODUCT, hur man summerar 2d-intervall med SUMPRODUCT och hur man summerar värden med OR-operatorn med hjälp av SUMPRODUCT med flera kriterier.