Hur man summerar efter datum i Excel utan tid

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

  1. Start med =SUMPRODUCT( funktion
  2. Typ av välj det intervall som innehåller värden $C$3:$C$16
  3. Använd asterisk för att produkt intervall värden med villkor intervall och öppna en parentes *(
  4. Fortsätt med INT($B$3:$B$16) för att ta bort tidsdelen
  5. Inför villkor med ett likhetstecken och kriterieområde =E3
  6. 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.

Lämna ett svar

Din e-postadress kommer inte publiceras.