En av de vanligaste frågorna på anslagstavlan är hur man använder SumIf-funktionen med två olika villkor. Tyvärr är svaret att SumIf inte kan hantera två olika villkor.
För att göra två villkor måste du använda en ganska komplicerad matrisformel. Med tillägget för villkorlig sumguide kan du enkelt ange dessa komplicerade formler.
Här är ett Excel-kalkylblad med kolumner för produkt, säljare och försäljning. Uppgifterna finns i cellerna A2: C29.
Om du vill summera försäljningen fungerar en enkel SUM () -funktion. =SUM(C2:C29).
Många Excellers upptäcker funktionen SumIf. Med den här funktionen är det ganska enkelt att räkna ut den totala försäljningen för produkten ABC.=SUMIF(A2:A29,E2,C2:C29)
Det är också lätt att räkna ut den totala försäljningen av försäljningsrepresentanten Joe med =SUMIF(B2:B29,E2,C2:C29).
Du antar då att det är möjligt att räkna ut den totala försäljningen av produkten ABC som gjorts av Joe. Det finns dock inget sätt att göra detta med SumIf-funktionen. Det visar sig att du måste använda en ganska komplex array eller CSE-formel.
Låt oss inse det - Sum-formeln är Excel 101. SumIf-formeln ligger inte långt efter i komplexitet. Men CSE-formeln för att beräkna den totala ABC-försäljningen från Joe är tillräcklig för att få mitt huvud att snurra.
De goda nyheterna - Microsoft erbjuder guiden Villkorlig summa som tillåter även en nybörjare att ange komplexa villkorliga formler baserade på 1, 2 eller fler villkor. Guiden Villkorlig summa är ett tillägg. För att lägga till denna funktionalitet i Excel, gå till menyn Verktyg och välj Tillägg. Markera kryssrutan bredvid guiden Villkorlig summa i dialogrutan Tillägg och välj OK. Det är möjligt att du kanske behöver din installations-CD nu, eftersom Microsoft inte inkluderar guiden i standardinstallationen.
När tillägget har slagits på kommer det att finnas ett villkorligt Sum … val längst ner på Verktyg-menyn.
Välj en enda cell i din dataset och välj Verktyg - villkorad summa. Förutsatt att dina data är snyggt formaterade med en enda rubrikrad, kommer Excel att gissa ordentligt på räckvidden för dina data. Välj Nästa.
I steg 2 väljer du den kolumn som ska summeras. I det här fallet gissade guiden redan att du vill summera den första (och enda) numeriska kolumnen - Försäljning. I mitten av dialogrutan finns tre rullgardinsreglage. Dessa råkar vara korrekta för det första villkoret - Produkten är lika med ABC, så välj knappen Lägg till villkor.
Sedan kan du lägga till ditt andra tillstånd. I det här fallet vill du ange att försäljaren är Joe. Välj pilen för den första rullgardinsmenyn. Excel erbjuder en alfabetisk lista över tillgängliga kolumnnamn. Välj säljare.
Centreringsmenyn är korrekt, men för fullständighet här kan du se att du kunde ha valt lika, mindre än, större än, mindre än eller lika, större än eller lika eller inte lika.
Välj Joe från den tredje rullgardinsmenyn.
Välj knappen Lägg till villkor.
Du är nu redo att gå till steg 3. Tryck på Nästa-knappen.
I steg 3 har du två val. I första valet kommer guiden att ange en enda formel med värdena "ABC" och "Joe" hårdkodade i formeln. Det kommer att ge dig svaret, men det finns ingen möjlighet att enkelt ändra formeln. Med andra valet kommer Excel att ställa in en ny cell med värdet "ABC" och en ny cell med värdet "Joe". En tredje cell innehåller formeln som gör en villkorad summa baserat på de två värdena. Med det här alternativet kan du skriva nya värden i cellerna för att se de totala XYZ som sålts av Adam.
Guiden frågar sedan var du vill ha värdet för ABC. Välj en cell och välj Nästa. Upprepa när guiden ber dig välja en cell för Joe och formeln.
När du väljer Slutför i det sista steget skapar Excel en något annorlunda (men giltig) version av CSE-formeln.
Denna formel beräknar att Joe sålde 33 338 dollar i ABC.
Om du ändrar produktinmatningscellen från ABC till DEF, beräknas formeln på nytt för att visa att Joe sålde 24 478 $ i DEF.
Guiden Villkorlig summa placerar komplexa formler inom räckhåll för alla Excel-ägare.
Ytterligare information:Om du vill bygga en tabell som visar varje produkts försäljning av varje säljare finns det speciell "vård och matning" som du behöver veta om dessa formler. Skriv varje säljare överst i intervallet. Skriv varje produkt i den vänstra kolumnen i sortimentet. Redigera formeln från guiden. I bilden nedan pekar formeln en produkten i cell E6. Denna referens måste verkligen vara $ E6. Om du lämnar referensen som E6 och kopierar formeln till kolumn G, ser formeln på F6 istället för E6 och detta skulle vara fel. Om du lägger till ett dollartecken före E i E6 ser du till att formeln alltid tittar på produkten i kolumn E. Formeln pekar också på en säljare i cell F5. Denna referens måste verkligen vara F $ 5. Om du lämnade referensen som F5 och kopierade ner till rad 7,F5-referensen ändras till F6 och detta är inte korrekt. Att lägga till ett dollartecken före radnumret låser radnumret och referensen pekar alltid på rad 5.
I redigeringsläge (välj cellen och tryck på F2 för att redigera), skriv en $ före E. Skriv ett dollartecken före 5 i F5. Tryck inte på Enter än!
Denna formel är en speciell typ av formel. Om du trycker på Enter får du ett 0, vilket inte är korrekt.
Istället för att skriva Enter, håll ned Ctrl och Shift-tangenterna medan du trycker på Enter. Denna magiska kombination av C trl + S hift + E nter är därför jag kallar dessa CSE-formler.
Det finns en sista övervägelse innan du kopierar formeln till resten av tabellen. Din benägenhet kan vara att kopiera F6 och klistra in på F6: G8. Om du försöker detta kommer Excel att ge dig det förbryllande meddelandet "Du kan inte ändra del av en matris". Excel klagar över att du inte kan klistra in en CSE-formel i ett intervall som innehåller den ursprungliga CSE-formeln.
Det är lätt att kringgå detta. Kopiera F6. Klistra in till F7: F8.
Kopiera F6: F8. Klistra in till G6: G8. Du kommer att ha en tabell med CSE-formler som visar totalsummor baserat på två villkor.
