Generisk formel
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Sammanfattning
För att räkna unika värden med ett eller flera villkor kan du använda en formel baserad på UNIKT och FILTER. I exemplet som visas är formeln i H7:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
som returnerar 3, eftersom det finns tre unika namn i B6: B15 associerade med Omega-projektet.
Obs: den här formeln kräver dynamiska matrisformler, endast tillgängliga i Excel 365. Med en äldre version av Excel kan du använda mer komplexa alternativa formler.
Förklaring
Kärnan använder denna formel UNIQUE-funktionen för att extrahera unika värden, och FILTER-funktionen tillämpar kriterier.
FILTER-funktionen arbetar inifrån och ut och används för att tillämpa kriterier och endast extrahera namn som är associerade med "Omega" -projektet:
FILTER(B6:B15,C6:C15=H6) // Omega names only
Resultatet från FILTER är en matris så här:
("Jim";"Jim";"Carl";"Sue";"Carl")
Därefter används UNIQUE-funktionen för att ta bort dubbletter:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
vilket resulterar i en ny array som denna:
("Jim";"Carl";"Sue") // after UNIQUE
Vid den här tiden har vi en unik lista med namn associerade med Omega, och vi behöver bara räkna dem. Av skäl som förklaras nedan gör vi detta med LEN-funktionen och SUM-funktionen. För att göra saker tydliga skriver vi om formeln för att inkludera den unika listan:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
LEN-funktionen får längden på varje objekt i listan och returnerar en rad längder:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Därefter kontrollerar vi om längderna är större än noll:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
Och använd en dubbel negativ för att tvinga SANT och FALSKT till 1s och 0s:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Slutligen lägger vi till resultaten med SUM-funktionen:
=SUM((1;1;1)) // returns 3
Denna matris levereras direkt till COUNTA-funktionen, som returnerar ett slutligt antal:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Observera att eftersom vi kontrollerar längden på varje artikel som returneras av UNIK, ignoreras tomma eller tomma celler som uppfyller kriterierna. Denna formel är dynamisk och kommer att beräkna omedelbart om källdata ändras.
Räkna unikt med flera kriterier
För att räkna unika värden baserat på flera kriterier kan du utvidga "inkludera" -logiken i FILTER. Till exempel, för att bara räkna unika namn för Omega-projektet i juni, använd:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Detta är ett exempel på att använda boolesk logik för att tillämpa mer än ett villkor. Tillvägagångssättet förklaras mer detaljerat här.
För mer information, se den här träningsvideon: Hur man filtrerar med flera kriterier.
COUNTA
Det är möjligt att skriva en enklare formel som svarar på COUNTA-funktionen. En viktig varning är dock att COUNTA returnerar 1 när det inte finns några matchande värden. Detta beror på att FILTER-funktionen returnerar ett fel när inga data matchar kriterierna, och detta fel slutar räknas av COUNTA-funktionen. Den grundläggande formen för COUNTA ser ut så här:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Återigen kommer denna formel att returnera 1 när det inte finns någon matchande data. Det kommer också att innehålla tomma celler som uppfyller kriterierna. Formeln baserad på LEN och SUM är ett bättre alternativ.
Inga dynamiska matriser
Om du använder en äldre version av Excel utan stöd för dynamisk matris kan du använda en mer komplex formel. För en mer allmän diskussion om dynamiska matrisalternativ, se: Alternativ till dynamiska matrisformler.
