Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.

Nou pe simpatie:
morena28 pe Simpatie

Femeie
25 ani
Bistrita Nasaud
cauta Barbat
27 - 52 ani

Mrrrr's Forum (VIEW ONLY)Reguli Inregistrare Login PozeNu sunteti logat. Lista Forumurilor Pe Tematici

Mrrrr's Forum (VIEW ONLY) / Tutoriale si Ghiduri Utile // Tutorials and useful guides /

[EXCEL] The Many Ways to Count Values with Multiple Conditions

Pagini: 1

Moderat de TRaP, TonyTzu

pus acum 2 luni#1

TRaP
Moderator
Postari: 960

In range N57:N63 I have a bunch of ages, and want to count how many are above or equal to 30 and below or equal to 50.

There are multiple functions one can use to calculate this, from simple ones to overkill ones for just this simple task - those might work well for more complicated applications.

Here we go:

1. Simplest way: COUNTIFS (uses the 2 conditions above)

=COUNTIFS(N57:N63; ">=30"; N57:N63; "<=50")

2. COUNTIF

=COUNTIF(N57:N63; ">=30") - COUNTIF(N57:N63; ">50")

Note: Second COUNTIF changes the original condition of <=50 to >50

3. SUMPRODUCT

=SUMPRODUCT((N57:N63>=30) * (N57:N63<=50))

4. SUM

=SUM((N57:N63>=30)*(N57:N63<=50))

Note: requires input with CTRL+SHIFT+ENTER in Excel 2019 or older

5. FILTER + COUNTA

=COUNTA(FILTER(N57:N63; (N57:N63>=30) * (N57:N63<=50)))

Note: works unless the filtered cells contain blanks, needs additional condition if they do.

6. LET + FILTER + COUNTA

=LET(
rng; N57:N63;
filter; FILTER(rng; (rng>=30) * (rng<=50));
COUNTA(filter)
)

Note: Basically same as FILTER + COUNTA, but easier update if range changes a lot.

7. FILTER + ROWS

=ROWS(FILTER(N57:N63;(N57:N63>=30)*(N57:N63<=50)))

Note: similar to FILTER + COUNTA but counts rows instead of non-blanks (COUNTA)

8. BYROW + LAMBDA + SUM

=SUM(BYROW(N57:N63;LAMBDA(x;--AND(x>=30;x<=50))))

9. MAP + LAMBDA + SUM

=SUM(MAP(N57:N63;LAMBDA(x;--AND(x>=30;x<=50))))

Source:
ChatGPT & Gemini

pus acum 11 zile#2

TRaP
Moderator
Postari: 960

For non-numeric values:

1. COUNTIFS

=COUNTIFS(O6:O487;"<>";O6:O487;"<>N/A")

Note: might not return correct results for blank cells

2. FILTER+COUNTA

=COUNTA(FILTER(O6:O487; (O6:O487<>"")*(O6:O487<>"N/A")))

Note: requires Excel 2021 or newer

3. SUM

=SUM((O6:O487<>"")*(O6:O487<>"N/A"))

Note: requires Excel 365/Excel 2024

4. COUNT

=COUNT(O6:O487)

Note: only if data you want to count is numbers, cause COUNT ignores all text

Source for 1, 3, 4: ChatGPT

Pagini: 1

Mergi la