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:
Larina23
 | Femeie
19 ani
Timis
cauta Barbat
28 - 80 ani |
|
|
TRaP
Moderator
Inregistrat: acum 7 ani
Postari: 836
|
|
I have a range of cells that also contain text in the cells with numbers. It is always the same text "REST" followed by a space and then the number.
I needed a formula to sum the numbers in the range.
Example:
- if in range there would be 5 cells completed with (without quotes): "REST 2", "REST 10", "REST 5", "REST 3", "REST 33"
- the formulas would return the sum of 2 + 10 + 5 + 3 + 33 = 53
ChatGPT solution (CTLR+SHIFT+ENTER formula in Excel 2019 or older, just ENTER in newer versions of Excel 2021+):
=SUM(IF(ISNUMBER(FIND("REST"; J3:J29)); VALUE(MID(J3:J29; FIND("REST"; J3:J29) + 5; 10)); 0))
Copilot solution (formula that works regardless of word existing besides the number, as long as it is 1 word separated from the number by 1 space and the number is in the end of the cell)
=SUMPRODUCT(IFERROR(--MID(J3:J29; SEARCH(" "; J3:J29) + 1; LEN(J3:J29) - SEARCH(" "; J3:J29)); 0))
|
|
| pus acum 1 luna |
|
|
TRaP
Moderator
Inregistrat: acum 7 ani
Postari: 836
|
|
Updated Copilot solution to include a substitution prior to going through the formula above.
cleanedRange first removes the string "LA " from range, then the formula proceeds to extract the numbers and sum them up.
On the first row of LET there is a comment containing the old formula for me to have in Excel, just in case.
=LET(
comment; "G1+SUMPRODUCT(IFERROR(--MID(INDIRECT(range_below); SEARCH(space_btw_quotes; INDIRECT(see_range_below)) + 1; LEN(INDIRECT(range_below)) - SEARCH(space_btw_quotes; INDIRECT(range_below))); 0))";
range; INDIRECT("J"&K35&":J"&L35);
cleanedRange; SUBSTITUTE(range; "LA "; "");
extractedNumbers; IFERROR(--MID(cleanedRange; SEARCH(" "; cleanedRange) + 1; LEN(cleanedRange) - SEARCH(" "; cleanedRange)); 0);
G1Val; G1;
G1Val + SUMPRODUCT(extractedNumbers)
)
|
|
| pus acum 3 saptamani |
|