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:
brunetyk_20 pe Simpatie.ro
 | Femeie
20 ani
Vrancea
cauta Barbat
21 - 47 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
I wanted a dynamic range: row()+3, row()+503, that's why the formula is so long.
This one worked for my needs:
- count unique codes in column G that have errors in column K
Array formula, CTRL+SHIFT+ENTER:
=SUM(--(FREQUENCY(IF(ISERROR(INDEX(K:K;ROW()+3):INDEX(K:K;ROW()+502)) * NOT(ISBLANK(INDEX(G:G;ROW()+3):INDEX(G:G;ROW()+502))); MATCH(INDEX(G:G;ROW()+3):INDEX(G:G;ROW()+502); INDEX(G:G;ROW()+3):INDEX(G:G;ROW()+502); 0)); ROW(INDEX(G:G;ROW()+3):INDEX(G:G;ROW()+502))-ROW(INDEX(G:G;ROW()+3))+1)>0))
Source: ChatGPT 3.5
ChatGPT also provided 2 other solutions, one using INDIRECT and one using OFFSET, but both returned #VALUE! error for me. The one above worked fine, but below are the 2 other solutions applying for my needs as well (except for the error) - both formulas are array formulas:
Using INDIRECT:
=SUM(--(FREQUENCY(IF(ISERROR(INDIRECT("K"&(ROW()+3)&":K"&(ROW()+500))) * NOT(ISBLANK(INDIRECT("G"&(ROW()+3)&":G"&(ROW()+500)))); MATCH(INDIRECT("G"&(ROW()+3)&":G"&(ROW()+500)); INDIRECT("G"&(ROW()+3)&":G"&(ROW()+500)); 0)); ROW(INDIRECT("G"&(ROW()+3)&":G"&(ROW()+500)))-ROW(INDIRECT("G"&(ROW()+3)))+1)>0))
Using OFFSET: - this should also be written with INDIRECT for K$131 / G$131
=SUM(--(FREQUENCY(IF(ISERROR(OFFSET(K$131;ROW()+3-1;0;500;1)) * NOT(ISBLANK(OFFSET(G$131;ROW()+3-1;0;500;1))); MATCH(OFFSET(G$131;ROW()+3-1;0;500;1); OFFSET(G$131;ROW()+3-1;0;500;1); 0)); ROW(OFFSET(G$131;ROW()+3-1;0;500;1))-ROW(OFFSET(G$131;ROW()+3-1;0;500;1))+1)>0))
|
|
| pus acum 7 luni |
|