Use it to add up values that meet one or more conditions.
=SUMIFS(A1:A100, B1:B100, "NZ", C1:C100, ">20") adds up all values in A1:A100 where column B is NZ and column C is more than 20.
Excel has 100s of functions. Most them are not useful in day to day business setting. Here are the 10 most useful functions based on my 15 years of regular usage.
🧵
Use it to add up values that meet one or more conditions.
=SUMIFS(A1:A100, B1:B100, "NZ", C1:C100, ">20") adds up all values in A1:A100 where column B is NZ and column C is more than 20.
Use it to lookup a value and get corresponding value.
=XLOOKUP("Widget10", A1:A100, B1:B100)
looks the code Widget10 in column A and returns the price in column B.
* Works in Excel 365 / Web only
Use it to filter a list or range based on the conditions. Returns a range of values and automatically spills the values into next cells!
=FILTER(A1:C100, B1:B100="NZ")
returns the entire range of A1:C100 where column B has NZ.
Use it to count items that meet one or more criteria.
=COUNTIFS(B1:B100, "NZ", C1:C100, ">20")
tells us how many items have column B as NZ and column C more than 20.
Use to it get a specific items from a list or range.
=INDEX(A1:A100, 12)
returns the 12th item in the range A1:A100
Use it to calculate a future or past date after x months.
=EDATE(TODAY(), 6)
tells you what the date is exactly 6 months from now.
Use it to combine text values in range with a specified delimiter.
=TEXTJOIN(" ",,"This","tweet","thread","is","awesome!")
returns the message "This tweet thread is awesome!"
¹ Works in Excel 2019 or above
Use it to extract unique values from a range.
=UNIQUE(A1:A100)
gives you the unique items in range A1:A100 by removing any any duplicates.
Use it to sort a list or range by in ascending or descending order.
=SORT(A1:C100, 2, -1)
Sorts the range A1:C100 by column B(2nd column) in descending order (-1)
Use it to replace any formula errors with a relevant value.
=IFERROR(A1/0, "We can retweet this thread, but can't really divide by zero")
prints the message "We can retweet this thread, but can't really divide by zero"😉
Top 15 Excel functions 👉 https://t.co/89m2aM6fYF
Advanced Excel Skills for you 👉 https://t.co/uRW5resifJ
More from All
You May Also Like
So it's now October 10, 2018 and....Rod Rosenstein is STILL not fired.
He's STILL in charge of the Mueller investigation.
He's STILL refusing to hand over the McCabe memos.
He's STILL holding up the declassification of the #SpyGate documents & their release to the public.
I love a good cover story.......
The guy had a face-to-face with El Grande Trumpo himself on Air Force One just 2 days ago. Inside just about the most secure SCIF in the world.
And Trump came out of AF1 and gave ol' Rod a big thumbs up!
And so we're right back to 'that dirty rat Rosenstein!' 2 days later.
At this point it's clear some members of Congress are either in on this and helping the cover story or they haven't got a clue and are out in the cold.
Note the conflicting stories about 'Rosenstein cancelled meeting with Congress on Oct 11!"
First, rumors surfaced of a scheduled meeting on Oct. 11 between Rosenstein & members of Congress, and Rosenstein just cancelled it.
He's STILL in charge of the Mueller investigation.
He's STILL refusing to hand over the McCabe memos.
He's STILL holding up the declassification of the #SpyGate documents & their release to the public.
I love a good cover story.......
The guy had a face-to-face with El Grande Trumpo himself on Air Force One just 2 days ago. Inside just about the most secure SCIF in the world.
And Trump came out of AF1 and gave ol' Rod a big thumbs up!
And so we're right back to 'that dirty rat Rosenstein!' 2 days later.
At this point it's clear some members of Congress are either in on this and helping the cover story or they haven't got a clue and are out in the cold.
Note the conflicting stories about 'Rosenstein cancelled meeting with Congress on Oct 11!"
First, rumors surfaced of a scheduled meeting on Oct. 11 between Rosenstein & members of Congress, and Rosenstein just cancelled it.
Rep. Andy Biggs and Rep. Matt Gaetz say DAG Rod Rosenstein cancelled an Oct. 11 appearance before the judiciary and oversight committees. They are now calling for a subpoena. pic.twitter.com/TknVHKjXtd
— Ivan Pentchoukov \U0001f1fa\U0001f1f8 (@IvanPentchoukov) October 10, 2018
👨💻 Last resume I sent to a startup one year ago, sharing with you to get ideas:
- Forget what you don't have, make your strength bold
- Pick one work experience and explain what you did in detail w/ bullet points
- Write it towards the role you apply
- Give social proof
/thread
"But I got no work experience..."
Make a open source lib, make a small side project for yourself, do freelance work, ask friends to work with them, no friends? Find friends on Github, and Twitter.
Bonus points:
- Show you care about the company: I used the company's brand font and gradient for in the resume for my name and "Thank You" note.
- Don't list 15 things and libraries you worked with, pick the most related ones to the role you're applying.
-🙅♂️"copy cover letter"
"I got no firends, no work"
One practical way is to reach out to conferences and offer to make their website for free. But make sure to do it good. You'll get:
- a project for portfolio
- new friends
- work experience
- learnt new stuff
- new thing for Twitter bio
If you don't even have the skills yet, why not try your chance for @LambdaSchool? No? @freeCodeCamp. Still not? Pick something from here and learn https://t.co/7NPS1zbLTi
You'll feel very overwhelmed, no escape, just acknowledge it and keep pushing.
- Forget what you don't have, make your strength bold
- Pick one work experience and explain what you did in detail w/ bullet points
- Write it towards the role you apply
- Give social proof
/thread
"But I got no work experience..."
Make a open source lib, make a small side project for yourself, do freelance work, ask friends to work with them, no friends? Find friends on Github, and Twitter.
Bonus points:
- Show you care about the company: I used the company's brand font and gradient for in the resume for my name and "Thank You" note.
- Don't list 15 things and libraries you worked with, pick the most related ones to the role you're applying.
-🙅♂️"copy cover letter"
"I got no firends, no work"
One practical way is to reach out to conferences and offer to make their website for free. But make sure to do it good. You'll get:
- a project for portfolio
- new friends
- work experience
- learnt new stuff
- new thing for Twitter bio
If you don't even have the skills yet, why not try your chance for @LambdaSchool? No? @freeCodeCamp. Still not? Pick something from here and learn https://t.co/7NPS1zbLTi
You'll feel very overwhelmed, no escape, just acknowledge it and keep pushing.