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.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
simobuzedulci_o
 | Femeie
24 ani
Dambovita
cauta Barbat
25 - 47 ani |
|
|
TRaP
Moderator
Inregistrat: acum 8 ani
Postari: 931
|
|
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 5 zile |
|