MA
Mohammad A. Al-Asmi
1 year ago

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",""))))

2 Likes

Replies

Marcos Leiva 1 year ago

Creo que ArrayFormula es incompatible con sum, no así con sumif

1 Like
Marcos Leiva (1 year ago)

Por otro lado creo que lo mejor es hacerlo con query que te da más flexibilidad para aplicar diversos criterios.

Marcos Leiva (1 year ago)

Comparte una copia de tu libro con modo de edición y te ayudo si deseas.

Mohammad A. Al-Asmi (1 year ago)

Marcos Leiva I do thank you for your advice, this is it https://docs.google.com/spreadsheets/d/1XCF_SziXXe3jZquAn_0oN3qPeI8kZT98mYIhduF2Pjc/edit?usp=drivesdk

Mohammad A. Al-Asmi (1 year ago)

Marcos Leiva I hope you can make it, Thank you for your efforts!

Phoenix Ice (1 year ago)

Mohammad A. Al-Asmi

Mohammad A. Al-Asmi (1 year ago)

Phoenix Ice Thank you very much, sir. I did some tests and

  • how can I make it dynamic like if I add new row above row 2 and it was empty , the table below counts it ?
  • How can I pin (spent), not spent?
  • I want spent an spent transfer be summed together not separate

Phoenix Ice (1 year ago)

Mohammad A. Al-Asmi You check the file :)

Mohammad A. Al-Asmi (1 year ago)

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

Mohammad A. Al-Asmi (1 year ago)

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

Phoenix Ice (1 year ago)

Mohammad A. Al-Asmi You try again

Mohammad A. Al-Asmi (1 year ago)

Phoenix Ice I appreciate Thank you very much

Mohammad A. Al-Asmi (1 year ago)

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 1 year ago

.

0 Likes
Mohammad A. Al-Asmi 1 year ago

.

0 Likes
Mohammad A. Al-Asmi 1 year ago

.

0 Likes
Mohammad A. Al-Asmi 1 year ago

.

0 Likes
Mohammad A. Al-Asmi 1 year ago

.

0 Likes
Mohammad A. Al-Asmi 1 year ago

.

0 Likes
Fiktur Fox 1 year ago

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)))

0 Likes
Mohammad A. Al-Asmi (1 year ago)

Fiktur Fox May you apply it please? https://docs.google.com/spreadsheets/d/1-CA3H5nIYnw3TYBbyjeF0iVlSdMmb_M5Z2mLRi_Y3pQ/edit?usp=drivesdk

Fiktur Fox (1 year ago)

Mohammad A. Al-Asmi Done in a separate cell.

Mohammad A. Al-Asmi (1 year ago)

Fiktur Fox What impressive GENIUS! THANK YOU!!!!

Mohammad A. Al-Asmi (1 year ago)

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

Fiktur Fox (1 year ago)

Mohammad A. Al-Asmi changed the formula, you do some testing..

Mohammad A. Al-Asmi (1 year ago)

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?

Fiktur Fox (1 year ago)

Mohammad A. Al-Asmi mailto:kechag@gmail.com

Mohammad A. Al-Asmi (1 year ago)

Fiktur Fox Done

Mohammad A. Al-Asmi (1 year ago)

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.

Fiktur Fox (1 year ago)

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)),"-|"))}

Mohammad A. Al-Asmi (1 year ago)

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.

Fiktur Fox (1 year ago)

Mohammad A. Al-Asmi no worries bro. good luck on your project. hope you're still on time..

Mohammad A. Al-Asmi (1 year ago)

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}})