Ladda ner exempelarbetsboken
Denna handledning visar hur du använder SUMIFS -funktionen för att summera data som motsvarar tomma eller tomma celler i Excel och Google Sheets.
Summa om tom
Först kommer vi att visa hur man summerar rader med tomma celler.
SUMIFS -funktionen summerar data som uppfyller vissa kriterier.
Vi kan använda SUMIFS -funktionen för att summera allt Poäng för Spelare utan namn i exemplet nedan.
| 1 | = SUMIFIER (C3: C8, B3: B8, "") |
Vi använder dubbla citattecken (“”) för att representera en tom cell i Excel. Vårt exempel ignorerar Spelare A, B, C och D och summerar poängen för okända Spelare.
Behandla utrymmen som tomma celler - med hjälpkolumn
Du måste vara försiktig när du interagerar med tomma celler i Excel. Celler kan visas tomma för dig, men Excel behandlar dem inte som tomma. Detta kan inträffa om cellen innehåller mellanslag, radbrytningar eller andra osynliga tecken. Detta är ett vanligt problem vid import av data till Excel från andra källor.
Om vi behöver behandla celler som bara innehåller mellanslag på samma sätt som om de vore tomma, fungerar inte formeln i föregående exempel. Lägg märke till hur SUMIFS -formeln inte anser att cell B9 nedan (”“) är tom:
För att behandla en cell som bara innehåller mellanslag som om det vore en tom cell kan vi lägga till en hjälparkolumn med TRIM -funktionen för att ta bort de extra mellanslagen från varje cellvärde:
| 1 | = TRIM (B3) |
Vi tillämpar SUMIFS -funktionen på hjälparkolumnen, och den beräknar nu summan exakt.
| 1 | = SUMIFIER (E3: E9, D3: D9, "") |
Hjälpkolumnen är lätt att skapa och lätt att läsa, men du kanske vill ha en enda formel för att utföra uppgiften. Detta behandlas i nästa avsnitt.
Behandla utrymmen som tomma celler - utan hjälpkolumn
Om en hjälparkolumn inte är lämplig för dina behov kan du använda SUMPRODUCT -funktionen i kombination med LEN- och TRIM -funktionerna för att summera tomma rader.
| 1 | = SUMPRODUCT (-(LENG (TRIM (B3: B9)) = 0), D3: D9) |
I det här exemplet använder vi SUMPRODUCT -funktionen för att utföra en komplicerad "summa om" -beräkning. Låt oss gå igenom formeln.
Detta är vår sista formel:
| 1 | = SUMPRODUCT (-(LENG (TRIM (B3: B9)) = 0), D3: D9) |
Först listar SUMPRODUCT -funktionen mängden värden från de två cellområdena:
| 1 | = SUMPRODUCT (-(LEN (TRIM ({"A"; "B"; ""; "C"; ""; "XX"; ""})) = 0), {25; 10; 15; 5 ; 8; 17; 50) |
TRIM -funktionen tar sedan bort ledande och bakre mellanslag från Spelare namn:
| 1 | = SUMPRODUCT (-(LEN ({"A"; "B"; ""; "C"; ""; "XX"; ""}) = 0), {25; 10; 15; 5; 8; 17; 50) |
LEN -funktionen beräknar längden på den trimmade Spelare namn:
| 1 | = SUMPRODUKT (-({1; 1; 0; 1; 0; 2; 0} = 0), {25; 10; 15; 5; 8; 17; 50) |
Med det logiska testet (= 0) trimmas alla Spelare namn med 0 tecken ändras till TRUE:
| 1 | = SUMPRODUKT (-({FALSKT; FALSKT; SANT; FALSKT; FALSKT; SANT}), {25; 10; 15; 5; 8; 17; 50) |
Därefter konverterar de dubbla streckarna (-) värdena TRUE och FALSE till 1s och 0s:
| 1 | = SUMPRODUKT ({0; 0; 1; 0; 1; 0; 1}, {25; 10; 15; 5; 8; 17; 50) |
SUMPRODUCT -funktionen multiplicerar sedan varje par poster i matriserna för att producera en array med Poäng bara för Spelare namn som är tomma eller endast skapade från mellanslag:
| 1 | = SUMPRODUKT ({0; 0; 15; 0; 8; 0; 50) |
Slutligen summeras siffrorna i arrayen:
| 1 | =73 |
Mer information om hur du använder booleska uttalanden och kommandot “-” i en SUMPRODUCT-funktion hittar du här.
Summa om tomt i Google Kalkylark
Dessa formler fungerar exakt likadant i Google Kalkylark som i Excel.
