Laskeminen useiden ehtojen perusteella COUNTIFS-funktiolla

Käytetään samaa myyntitaulukkoa — sarakkeessa A on Region, sarakkeessa B Month ja sarakkeessa C Amount — ja lasketaan, kuinka monta East-alueen tammikuun myyntiä on.

Tässä kaavassa ei ole määräsaraketta, koska laskemme rivien emmekä rahamäärien lukumäärää. Jokainen pari rajaa laskettavien rivien määrää lisää.

COUNTIFS hyväksyy SUMIFS-funktion tavoin vertailuoperaattorit lainausmerkeissä numeerisia testejä varten.

• ">100" — arvot, jotka ovat suurempia kuin 100
• ">=18" — vähintään 18-vuotiaat
• "<>" — kaikki ei-tyhjät solut

Tässä laskemme East-alueen rivit, joilla määrä on suurempi kuin 100. Huomatkaa, että määräsarake esiintyy criteria_range-alueena eikä sum_range-alueena.

Usein on tarpeen laskea arvot, jotka ovat kahden luvun välillä. Tätä ei voi tehdä yhdellä ehdolla, vaan sama sarake annetaan kahdesti ala- ja ylärajoineen.

Tällä lasketaan määrät, jotka ovat vähintään 50 ja enintään 200. Molemmat ehdot viittaavat sarakkeeseen C, ja koska COUNTIFS käyttää AND-logiikkaa, vain väliin kuuluvat rivit lasketaan.

Jotta rajoista tulee dynaamisia, tallentakaa ne soluihin ja yhdistäkää operaattori merkinnällä &. Kirjoittakaa alaraja soluun F1 ja yläraja soluun F2.

">="&F1 muodostaa ehdon suurempi tai yhtä suuri kuin F1. Näin käyttäjä voi kirjoittaa uudet rajat ja nähdä päivitetyn lukumäärän heti ilman, että kaavaa tarvitsee muokata.

Kaksi erityisehtoa on hyödyllinen tietojen laadun tarkistamisessa:

• "<>" laskee solut, jotka eivät ole tyhjiä
• "=" tai "" laskee solut, jotka ovat tyhjiä

Tällä kaavalla lasketaan, kuinka monella East-alueen rivillä on myyjä täytettynä eli sarake D ei ole tyhjä. Näin puuttuvat tiedot on helppo havaita.

Tekstiehdoissa tuetaan samoja jokerimerkkejä kuin SUMIFS-funktiossa. Käyttäkää merkkiä * minkä tahansa merkkijonon ja merkkiä ? yhden merkin paikalla.

Tällä lasketaan kaikki rivit, joilla myyjän nimi alkaa M-kirjaimella — nimet Maria, Mike ja Mohammed täsmäävät kaikki. Jokerimerkit vaikuttavat vain tekstiin; lukuihin niitä käytettäessä ne ohitetaan.

Sama mittoja koskeva sääntö kuin SUMIFS-funktiossa pätee myös tässä: kaikkien criteria_range-alueiden muodon on oltava sama. Jos yksi alue on A2:A100 ja toinen B2:B50, COUNTIFS palauttaa #VALUE!-virheen.

Kokonaisia sarakkeita, kuten A:A ja B:B, koskevat viittaukset ovat automaattisesti samankokoisia. Siksi ne ovat suosittu ja virheiltä suojaava valinta ehtoalueiksi.

COUNTIFS on monien koontinäyttöjen taustalla. Sijoittakaa alueiden nimet sarakkeeseen F ja kuukausien nimet riville 1. Laskekaa sitten jokainen yhdistelmä sekaviittauksilla, jotta kaava voidaan täyttää koko ruudukkoon.

Sarakkeiden lukitseminen viittauksilla $A:$A ja hakusolujen lukitseminen viittauksilla $F2 / G$1 antaa yhden kaavan täyttää koko ristiintaulukoidun lukumäärätaulukon, kun kaava kopioidaan.

Pidä funktioperheiden erot mielessä. COUNTIF käyttää yhtä range, criteria -paria, kun taas COUNTIFS käyttää yhtä tai useampaa paria. Toisin kuin SUMIFS-funktioilla, kummallakaan ei ole erillistä tulosaluetta — molemmat vain laskevat.

Jos ehtoja on useampi kuin yksi, käyttäkää COUNTIFS-funktiota. Monet käyttävät COUNTIFS-funktiota yhdenkin ehdon kanssa, jotta SUM-, COUNT- ja AVERAGE-funktioissa tarvitsee muistaa vain yksi yhtenäinen toimintatapa.

Osaatte nyt laskea useita ehtoja täyttävien rivien määrän:

• COUNTIFS(range1, crit1, range2, crit2, ...) — sum_range-aluetta ei ole, vaan ainoastaan testipareja.
• Laskekaa kahden raja-arvon välille osuvat arvot toistamalla sama sarake kahdella rajalla.
• Käyttäkää ei-tyhjille soluille ehtoa "<>" ja yhdistäkää soluviittauksiin operaattori merkillä &.
• Kaikkien alueiden on oltava samankokoisia.

Seuraavaksi lasketaan useita ehtoja täyttävien arvojen keskiarvo AVERAGEIFS-funktiolla.