ARRAYFORMULA in Google Sheets - 4 useful hacks included 🎁

Рет қаралды 90,389

Күн бұрын

Пікірлер: 92

@coupleracademy 3 жыл бұрын

Timestamps: 0:00 - Why should you use ARRAYFORMULA? 1:05 - ARAYFORMULA syntax in Sheets 2:31 - Remove unneeded values using a Railsware Academy hack! 6:48 - ARRAYFORMULA with nested IF 8:48 - Secure your ARRAYFORMULA and its output 9:15 - Use ARRAYFORMULA with texts 11:05 - ARRAYFORMULA + SUMIF 13:12 - Combine the column header and ARRAYFORMULA in a single cell 14:01 - SUMIFS workaround: use ARRAYFORMULA + SUMIF + &

@DinoRodriguez Жыл бұрын

This was simply superb, thank you!!!!!

@juanalfonzo7725 3 ай бұрын

I was looking for a formula that I didn't even know what it was called until I saw your channel, I really congratulate you, not only you explain extraordinarily well, but you really share value and practical knowledge. Congratulations, I have become a fan.

@coupleracademy 3 ай бұрын

Thank you so much!

@karinapl3595 3 жыл бұрын

Definitely the most comprehensive arrayformula tutorial that we could ever find on KZfaq! Thank you 🙌

@coupleracademy 3 жыл бұрын

Glad it was helpful!

@videomonkpvr Ай бұрын

Great way to combine header and formula into a single cell. Nice hack. Really appreciate it

@coupleracademy Ай бұрын

Yup, pretty neat! Thanks a lot :)

@teweldetesfay279 7 ай бұрын

I use formulas all the time and this lesson was a game changer for me, thank you for the information

@giniaweaver7377 2 жыл бұрын

Exactly what I needed!! I love how simple yet comprehensively you teach! Thank you so much.

@silvestrecamposano6317 18 күн бұрын

Thank you for the very comprehensive explanation...!

@coupleracademy 18 күн бұрын

You are welcome! Don't forget to subscribe to our channel so you will not miss another great tutorial.

@sarochb 10 ай бұрын

one of the best tutorial videos. you have a great teaching skill. not too fast, not too slow.

@coupleracademy 10 ай бұрын

thanks for the feedback, we appreciate it!

@ecommarketing Жыл бұрын

Thank you, this is a great explanation. IF(LEN(A2:A)= etc. is a great help!

@EduardoBicelisGarcia 3 жыл бұрын

Great video! great content. Love the way you handled the zoom. Thanks.

@coupleracademy 3 жыл бұрын

Thanks so much!

@EcoAku 2 жыл бұрын

Excellent tutorial!

@kakalkairuchi495 Жыл бұрын

Thank you!😊🙏 Learned something new very useful... 👌

@Tori_V_ 2 жыл бұрын

Best video about arrayformula!!! THx

@ryanculverwell 3 жыл бұрын

thank you, for always making it clear and easy to understand, always great to watch👌👍

@coupleracademy 3 жыл бұрын

Glad to hear that! Thank you for watching Railsware Product Academy:)

@2000sunsunny Жыл бұрын

Great tutorial. Thank you so much

@GiteshBajaj Жыл бұрын

Learned a lot in the video

@alexanderfisher6307 3 жыл бұрын

Thanks much for this video and other brilliant videos you made on the topic. You can also use ISBLANK() function instead of LEN()=0. I find it more readable. There is yet another way, which looks like IF(B2:B"", ) where means not equal and double quotes mean blank cell.

@coupleracademy 3 жыл бұрын

Thanks for sharing some useful tips here!

@2ndserve1 2 жыл бұрын

I like it!!! I needed this and the original LEN()=0 and the ISBLANK() both do the trick but the latter is a smidge more "elegant"

@costabulax 8 ай бұрын

Very comprehensive guide, thank you. Is it possible to jump every other row ?

@silvestrecamposano6317 11 ай бұрын

Thank you for the formula!...

@coupleracademy 11 ай бұрын

You're very welcome :)

@kelecsenyizoltan274 7 ай бұрын

Brilliant! Thanks.

@coupleracademy 7 ай бұрын

Thanks, we appreciate it!

@CENTOZINVEST 9 ай бұрын

Wonderful Thank you❤

@coupleracademy 9 ай бұрын

Our pleasure!

@Darry141 3 жыл бұрын

Definitely a thumb-up! :) Great educational content!

@coupleracademy 3 жыл бұрын

Thanks a lot!

@satyamchouhan4833 2 жыл бұрын

very helpful video , thaks for uploading it

@rudolphsalazar4974 3 жыл бұрын

You're the best, thank you so much

@lovitecmexico5772 2 жыл бұрын

gracias ARRAYFORMULA + SUMIF me sirvio de mucho, horas intentando con otra funcion hasta que vi el video me quedo a la primera.

@deveshkumarsharma843 2 жыл бұрын

Nice and helpful video, thanks

@KamleshSeemawat 2 жыл бұрын

Very useful functions.

@BukovTervicz 2 жыл бұрын

That was awesome

@user-cx5tq5hh2e 7 ай бұрын

🎯 Key Takeaways for quick navigation: 00:00 📊 *Introduction to Array Formula* - Array formula simplifies calculations in Google Sheets by distributing operations to multiple cells. 02:38 🧩 *Importing Data and Removing Zeros* - Importing data from Airtable to Google Sheets. - Removing unnecessary zeros using a combination of IF and LEN functions. 06:49 ✏️ *Combining Text Using Array Formula* - Combining text values, such as first and last names, using the ARRAYFORMULA function. 10:46 📊 *Using Array Formula with SUMIF* - Demonstrating how ARRAYFORMULA can be used with SUMIF to calculate totals based on a single condition. 12:37 📉 *Using Array Formula with SUMIFS* - Using ARRAYFORMULA with SUMIFS to handle multiple criteria and calculate totals for different categories. Made with HARPA AI

@AndreyAzimov 3 жыл бұрын

Great tips! Thanks you!

@coupleracademy 3 жыл бұрын

You are so welcome!

@leekspinner 8 ай бұрын

thank you!

@coupleracademy 8 ай бұрын

You're welcome!

@Levsjar Жыл бұрын

This is an amazing support - thank you. is there a way to prefill a new row with the same text?

@bankim0760 2 жыл бұрын

Very informative video. I have one question how we can use if and and in array formula

@MrCodai76 2 жыл бұрын

thank you very much

@sewaneeswimmingdiving6735 3 ай бұрын

Hello, I'm just learning formuals and was hoping that you use the countif and ifs statement using the array formula with range names?

@coupleracademy 3 ай бұрын

Hey there! 🙌 Glad to hear you're diving into formulas. Absolutely, you can use ARRAYFORMULA with COUNTIF and IFS statements, and you can definitely use named ranges to make things cleaner. For COUNTIF, you might do something like: =ARRAYFORMULA(COUNTIF(named_range, "criteria")) For IFS, it could look like this: =ARRAYFORMULA(IFS(named_range1 = "value1", result1, named_range2 = "value2", result2))

@mohdumarkhan7361 2 жыл бұрын

Wow greate mam

@thinhnguyenkhang63 2 жыл бұрын

The tutorial is very useful! But ARRAYFORMULA return values cross rows. How can I get the returned values cross columns?

@fancanvas 2 жыл бұрын

great

@Jminii 2 жыл бұрын

Could we use countif with arrayformular ??

@southhack Жыл бұрын

How can I make one column the date format and an other column time format using arrayformula?

@vardanvardanyan8025 2 жыл бұрын

thanks for 0s ))

@narayanaswamy.j1295 3 жыл бұрын

thank you mam

@coupleracademy 3 жыл бұрын

You are very welcome!

@arfaadaaftab9842 Жыл бұрын

hi i have file vlookup formula is not working on merge rows can you please help me

@VickyLatorreArt Жыл бұрын

Is there a formula that would take all the numbers before a decimal dot and add them without adding the numbers after the decimal dot. For example, numbers 2.00, 3.14, 2.70; sum numbers 2, 3, 2 in a formula and then the other numbers 00, 14, 70 sum them in another formula. OR better yet does anybody know about a google sheet template that can calculate and separate working regular hours from over time hours? Thank you.

@LotfyKozman 3 жыл бұрын

Are there any constraints that stop Arrayfunction from spilling its results?

@coupleracademy 3 жыл бұрын

As the output of an array formula is a range of values, inside the arrayformula you can’t use another formula that gives range as a result. For example, you can’t use Arrayformula(Filter()). Because Filter() will give, for example, 6 values in its output, thus, you will need all the rows multiplied by 6. The logic is broken. Make sure that the output of the operation you are using with Arrayformula gives a single output for each line.

@kevinnichols9836 2 жыл бұрын

HELP: Can I have Google Sheets split CSV in a column into row and have it replicate rows with the same information from the previous row, making sure that the number of rows created equals the number of commas in the previous row?

@abbesatty9498 2 жыл бұрын

Use IMPORTDATA(CSV_FILE_URL) formula if the CSV is a URL or File >> Import if the file is stored in your computer or in Google Drive..

@armarnijaywee Жыл бұрын

Will this array formulae work witth vlookup?

@AzeemaFaizunnisa 17 күн бұрын

Yes.

@saddi2u 3 жыл бұрын

I need to calculate last 3 days moving avg inside array formula so that whenever new rows are added, i should calculate last 3 days moving avg. I also have "daily avg" Colum and "Date" Colum to help calculate 3 days moving avg. Any idea how to do it?

@coupleracademy 3 жыл бұрын

hard to say without a dataset, sorry

@Nevir202 2 жыл бұрын

Yea, you can always make your range dynamic, say at current you need C7:C9 as those are the last 3, you can make something like INDIRECT("C"&(COUNTA(C1:C)-2)":C"&COUNTA(C1:C))

@celiatapetillo9305 2 жыл бұрын

How the ARRAYFORMULA works with sumif or countif?

@samuelcunha3548 2 жыл бұрын

How to use countuniqueifs with arrayformula??? Help me!!! 🙏🏼

@Chanderv31 7 ай бұрын

13:00

@mdvijai 2 жыл бұрын

How to use arrayformula with max function in each rows??

@castelinokelvin 2 жыл бұрын

Can you have multiple ArrayFormula in the same formula, example: =ARRAYFORMULA(INDEX('Form Responses 2'!A2:AN2,ARRAYFORMULA(MAX(COLUMN('Form Responses 2'!K2:AN2)*(--('Form Responses 2'!K2:AN2""))))))

@coldavenue2325 3 жыл бұрын

When I type =ARRAYFORMULA(B2:B*E2:E) it does not fill my column. Instead it says REF Error and demands me to add more rows. What is the problem?

@coupleracademy 3 жыл бұрын

You did not specify which function you want to distribute (e.g. arrayformula(SUM()) or arrayformula (A2:A * B2:B) )

@coldavenue2325 3 жыл бұрын

@@coupleracademy Actually I want googlefinance( formula for the stocks price to pull dynamically down as new tickers are added to the ticker column. The ARRAYFORMULA does not work with googlefinance( . Do you have any script idea how to get it done?

@coupleracademy 3 жыл бұрын

Try this: stackoverflow.com/questions/60874426/google-finance-as-an-argument-for-arrayformula

@mrb199uysimbas7 3 жыл бұрын

Anda hebat

@Videos_On_Internet 3 жыл бұрын

I love you mam

@kevinnichols9836 2 жыл бұрын

The problem with any Google tutorial is...the example are too simple when the practicality is complex due to limitations when its comes to easy-to-integrate with entire business systems that already exist.