Ladda ner exempelarbetsboken
Denna handledning visar hur du använder SUMIFS-funktionen för att summera data relaterade till icke-tomma eller icke-tomma celler i Excel och Google Sheets.
Summa om inte tomt
Först kommer vi att visa hur man summerar data avseende icke-tomma celler.
Vi kan använda SUMIFS -funktionen för att summera allt Poäng för Spelare med icke-tomma namn.
| 1 | = SUMIFIER (C3: C8, B3: B8, "") |
För att summera rader med icke-tomma celler utesluter vi Poäng med saknad Spelare namn. Vi använder kriterierna "inte lika med blank" ("") inuti SUMIFS -funktionen.
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:
| 1 | = SUMIFIER (D3: D9, B3: B9, "") |
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 LEN- och TRIM -funktionerna för att identifiera Spelare med namn.
TRIM -funktionen tar bort de extra mellanslagen från början och slutet av varje cellvärde och LEN -funktionen räknar sedan antalet återstående tecken. Om resultatet av LEN -funktionen är 0, då Spelare namnet måste ha varit tomt eller bara bestå av mellanslag:
| 1 | = LENG (TRIM (B3)) |
Vi tillämpar SUMIFS -funktionen på hjälparkolumnen (summering om den är större än 0), och den beräknar nu summan exakt.
| 1 | = SUMIFIER (E3: E9, D3: D9, "> 0") |
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 det är nödvändigt att behandla celler som bara innehåller mellanslag på samma sätt som om de vore tomma, men det är inte lämpligt att använda en hjälparkolumn, kan vi använda SUMPRODUCT -funktionen i kombination med LEN- och TRIM -funktionerna för att summera data relaterade till celler innehåller icke-blank Spelare namn:
| 1 | = SUMPRODUCT (-(LENG (TRIM (B3: B9))> 0), D3: D9) |
I det här exemplet använder vi SUMPRODUCT -funktionen för att utföra komplicerade "summa om" -beräkningar. 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) kan alla trimmas Spelare namn med mer än 0 tecken ändras till TRUE:
| 1 | = SUMPRODUKT (-({SANT; SANT; FALSKT; SANT; FALSKT; FALSKT}), {25; 10; 15; 5; 8; 17; 50) |
Därefter konverterar de dubbla streckarna (-) värdena TRUE och FALSE till 1s och 0s:
| 1 | = SUMPRODUKT ({1; 1; 0; 1; 0; 1; 0}, {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 inte är tomma eller inte bara skapas från mellanslag:
| 1 | = SUMPRODUKT ({25; 10; 0; 5; 0; 17; 0) |
Slutligen summeras siffrorna i matrisen tillsammans
| 1 | =57 |
Mer information om hur du använder booleska uttalanden och kommandot “-” i en SUMPRODUCT-funktion hittar du här
Summa om inte tomt i Google Kalkylark
Dessa formler fungerar exakt likadant i Google Kalkylark som i Excel.
