Hello friends!
In the second table I would like to sum data with if function or anysuitable function, how can I do this?
I mean I want to sum all (spent) and (spent(transfer)) together, and all (not spent) also together...
*Note:
I used for (Total) this code:
=ARRAYFORMULA(SUM(VALUE(REGEXREPLACE(OFFSET(B1,1,0,Counta(B1:B11)-1),"TL",""))))
Creo que ArrayFormula es incompatible con sum, no así con sumif
Por otro lado creo que lo mejor es hacerlo con query que te da más flexibilidad para aplicar diversos criterios.
Comparte una copia de tu libro con modo de edición y te ayudo si deseas.
Marcos Leiva I do thank you for your advice, this is it https://docs.google.com/spreadsheets/d/1XCF_SziXXe3jZquAn_0oN3qPeI8kZT98mYIhduF2Pjc/edit?usp=drivesdk
Marcos Leiva I hope you can make it, Thank you for your efforts!
Mohammad A. Al-Asmi
Phoenix Ice Thank you very much, sir. I did some tests and
Mohammad A. Al-Asmi You check the file :)
Phoenix Ice there is two problem😅, the first one is empty cell make the function shows N/A the second one that when I add a new row before row 2 it does not count with the sum function
Phoenix Ice i can add just your email so that we can chat in google sheet and no one change any thing by access to the link
Mohammad A. Al-Asmi You try again
Phoenix Ice I appreciate Thank you very much
Phoenix Ice Hello sir, I would like to ask you another question and I hope you answer it as soon as possible. How I can make google sheet print word "box" from D4:D12 even if i add new row before D4, I want to start printing it from D4 always, and D12 changes and moves according to how new rows have been added. Thanks.
Mohammad A. Al-Asmi Here's something to play around.. =Let(data, Offset(A1,1,0,Counta(A1:A11)-1,2), total, Sum(ArrayFormula(--REGEXEXTRACT(Index(data,,2) ,"[\d.]+"))), spent, Filter(data,Index(data,,1)<>"not spent"), notSpent, Filter(data,Index(data,,1)="not spent"), spentVal, Sum(ArrayFormula(--REGEXEXTRACT(Index(spent,,2) ,"[\d.]+"))), notSpentVal, Sum(ArrayFormula(--REGEXEXTRACT(Index(notSpent,,2) ,"[\d.]+"))), HStack(VStack("Total","Spent","Not Spent","Total Price"),VStack("Sum",spentVal,notSpentVal,total)))
Fiktur Fox May you apply it please? https://docs.google.com/spreadsheets/d/1-CA3H5nIYnw3TYBbyjeF0iVlSdMmb_M5Z2mLRi_Y3pQ/edit?usp=drivesdk
Mohammad A. Al-Asmi Done in a separate cell.
Fiktur Fox What impressive GENIUS! THANK YOU!!!!
Fiktur Fox Another thing please, how can I make it dynamic like if I add new row above row 2 and it was empty so the table below does not show N/A# I would like to show me the current sum even if some rows are empty
Mohammad A. Al-Asmi changed the formula, you do some testing..
Fiktur Fox i did, i think there is a problem with total😅 there is other thing: i notes that some people access to the file and start changing some values, can i add just your email? can you write it for me?
Mohammad A. Al-Asmi mailto:kechag@gmail.com
Fiktur Fox Done
Hello sir, I would like to ask you another question and I hope you answer it as soon as possible. How I can make google sheet print word "box" from D4:D12 even if i add new row before D4, I want to start printing it from D4 always, and D12 changes and moves according to how new rows have been added. Thanks.
Mohammad A. Al-Asmi Members in this group have different time zones. So don't expect your question to be solved "as soon as possible". In cell D3: ={"To";Transpose(SPLIT(TEXTJOIN("-",,REPT("box"&"|",Rows(D3:D12)-1)),"-|"))}
Fiktur Fox Thank you it works. Regarding your comment about "as soon as possible", I expected that there is different time zone, but the meaning I meant is that I am in a hurry and the deadline is close, so I do not wish to ignore it but to get help quickly if someone sees the comment. It seems there is misunderstanding.
Mohammad A. Al-Asmi no worries bro. good luck on your project. hope you're still on time..
Fiktur Fox Hello sir, back to this formula you have created, I just made the H column as custom number format which add TL to the number but still number not text, so I noticed the the sum formula below doesn't work until I add "TL" to the numbers as a test, should I delete any the functions that extract numbers from text? How I can do this, May you help sir?
🛑Note: If Arabic word makes any problem while editing the formula, I will change it immediately.
=LET(range, Offset(F3,1,0,Rows(F3:F4)-1,3), data, Filter(range,IFERROR(Index(range,,1),0)<>"",IFERROR(Index(range,,3),0)<>""), total, Sum(ArrayFormula(IFERROR(--REGEXEXTRACT(Index(data,,3) ,"[\d.]+"),0))), spent, Filter(data,IFERROR(Index(data,,1),0)<>"أموال غير قابلة للصرف"), notSpent, Filter(data,IFERROR(Index(data,,1),0)="أموال غير قابلة للصرف"), spentVal, Sum(ArrayFormula(IFERROR(--REGEXEXTRACT(Index(spent,,3) ,"[\d.]+"),0))), notSpentVal, Sum(ArrayFormula(IFERROR(--REGEXEXTRACT(Index(notSpent,,3) ,"[\d.]+"),0))), {{"المجموع";"الأموال القابلة للصرف";"الأموال الغير قابلة للصرف";"الأجمالي"}, {"القيمة";spentVal;notSpentVal;total}})