Kako koristiti COUNTIFS, SUMIFS, AVERAGEIFS u Excelu

Tri najčešće korištene formule u Excelu koje izvode jednostavne matematičke izračune su RAČUNATI, IZNOS i PROSJEČNA. Bilo da upravljate financijskim proračunom u Excelu ili jednostavno pratite vaš sljedeći odmor, vjerojatno ste prije koristili jednu od tih funkcija.

U ovom članku proći ćemo kroz osnove ovih triju funkcija i njihovih relevantnih i korisnih kolega: COUNTIFS, SUMIFS i AVERAGEIFS.

Recimo da pokrećemo novu internetsku tvrtku koja prodaje mobilne telefone, a imamo i listu s popisom prodaje koju smo napravili u prva dva mjeseca. Preuzmite primjer Excelove proračunske tablice ovdje.

Excel COUNT, SUM i AVERAGE

Da bismo znali koliko smo mobilnih telefona prodali, možemo brzo koristiti RAČUNATI formula prikazana u nastavku:

= COUNT (E2 E16)

S druge strane, da bismo dobili ukupan iznos prodaje koji smo napravili, možemo koristiti IZNOS formula prikazana u nastavku:

= SUM (E2 E16)

Konačno, kako bismo saznali prosječnu prodaju koju smo napravili za sve telefone, možemo koristiti PROSJEČNA formula:

= Srednja (E2 E16)

Rezultat bi trebao biti kao u nastavku:

Formula COUNT, SUM i AVERAGE funkcionirat će samo za zapise u kojima je vrijednost ćelije u formatu broja. Bilo koji zapis unutar raspona formule (tj. E2: E16 u ovom primjeru) neće biti u formatu broja.

Stoga provjerite jesu li sve ćelije u formuli COUNT, SUM i AVERAGE oblikovane kao Broj, ne Tekst. Pokušajte koristiti istu formulu, ali s E: E kao raspon umjesto E2: E16. Vratit će isti rezultat kao i prije, jer zanemaruje zaglavlje (tj. Prodajna cijena), koji je u tekstualnom obliku.

Sada, što ako želimo znati broj prodaje, ukupan iznos prodaje i prosječni iznos prodaje po telefonu, samo za one koji se prodaju u SAD-u? Ovdje igraju značajnu ulogu COUNTIFS, SUMIFS i AVERAGEIFS. Obratite pažnju na donju formulu:

COUNTIFS

Analiza formule:

1. = COUNTIFS ( - „=” označava početak formule u ćeliji i COUNTIFS je prvi dio Excelove funkcije koju koristimo.
2. D2: D16 - Odnosi se na raspon podataka kako bi se provjerilo zadovoljava li kriterij da bi se uključio u izračunavanje formule.
3. "SAD" - Kriteriji za traženje u navedenom rasponu podataka (D2: D16)
4. ) - Zatvaranje zagrada ukazuje na kraj formule.

Formula vraća 6 koji je broj prodaje proizvoda isporučenih iz skladišta u SAD-u.

SUMIFS

Analiza formule:

1. = SUMIFS ( - „=” ponovno označava početak formule.
2. E2: E16 - Odnosi se na raspon podataka koje bismo željeli ukupno, tj. Prodajnu cijenu u našem primjeru.
3. D2: D16 - Odnosi se na raspon podataka za provjeru zadovoljava li kriterije za uključivanje u ukupni iznos.
4. "SAD" - Kriteriji za traženje u navedenom rasponu podataka (D2: D16)
5. ) - Zatvaranje zagrada ukazuje na kraj formule.

Formula pokazuje $ 6.050 ukupne prodaje ostvarene za proizvode isporučene iz skladišta u SAD-u.

AVERAGEIFS

Analiza formule:

1. = AVERAGEIFS ( - „=” označava početak formule.
2. E2: E16 - Odnosi se na raspon podataka koje bismo željeli prosjek. U ovom primjeru želimo dobiti prosječni iznos prodaje za sve telefone koji se prodaju u SAD-u.
3. D2: D16 - Odnosi se na raspon podataka kako bi se provjerilo zadovoljava li kriterij za uključivanje u prosječnu formulu.
4. "SAD" - Kriteriji za traženje u navedenom rasponu podataka
5. ) - Zatvaranje zagrada ukazuje na kraj formule.

Formula pokazuje da smo proizvod prodali za oko $ 1.008 po telefonu u SAD-u.

Sve tri formule mogu uzeti više od jednog kriterija. Na primjer, ako želimo znati iste brojke (tj. RAČUNATI, IZNOS i PROSJEČNA) za proizvode prodane u SAD, ali posebno samo za Samsung samo trebamo dodati raspon podataka koji će se provjeriti, a zatim i njegove kriterije.

Pogledajte primjer u nastavku gdje se drugi kriterij dodaje početnim provjerama kriterija. (Plavi tekst označava prvi kriterij, a crveni drugi kriterij)

= COUNTIFS (D2: D16, "USA", B2: B16, "Samsung") = SUMIFS (E2: E16, D2: D16, "USA", B2: B16, "Samsung") = AVERAGEIFS (E2: E16, D2: D16, "USA", B2: B16, "Samsung")

Primijetit ćete da Excel također ima COUNTIF, SUMIF i AVERAGEIF formule bez sufiksa „S”. Oni se koriste slično COUNTIFS, SUMIFS i AVERAGEIFS. Međutim, oni bez sufiksa „S” u formuli imaju ograničenje dopuštanja samo jednog kriterija po formuli.

Budući da je sintaksa malo drugačija, preporučio bih da je koristite COUNTIFS, SUMIFS i AVERAGEIFS samo ako se može koristiti za jedan ili više kriterija, ako je potrebno. Uživati!