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
The best morning routine?
Starts the night before.
9 evening habits that make all the difference:
1. Write down tomorrow's 3:3:3 plan
• 3 hours on your most important project
• 3 shorter tasks
• 3 maintenance activities
Defining a "productive day" is crucial.
Or else you'll never be at peace (even with excellent output).
Learn more
2. End the workday with a shutdown ritual
Create a short shutdown ritual (hat-tip to Cal Newport). Close your laptop, plug in the charger, spend 2 minutes tidying your desk. Then say, "shutdown."
Separating your life and work is key.
3. Journal 1 beautiful life moment
Delicious tacos, presentation you crushed, a moment of inner peace. Write it down.
Gratitude programs a mindset of abundance.
4. Lay out clothes
Get exercise clothes ready for tomorrow. Upon waking up, jump rope for 2 mins. It will activate your mind + body.
Starts the night before.
9 evening habits that make all the difference:
1. Write down tomorrow's 3:3:3 plan
• 3 hours on your most important project
• 3 shorter tasks
• 3 maintenance activities
Defining a "productive day" is crucial.
Or else you'll never be at peace (even with excellent output).
Learn more
How to be 5x more productive.
— Ben Meer (@SystemSunday) August 1, 2022
A best-selling author\u2019s 3-3-3 Method:
2. End the workday with a shutdown ritual
Create a short shutdown ritual (hat-tip to Cal Newport). Close your laptop, plug in the charger, spend 2 minutes tidying your desk. Then say, "shutdown."
Separating your life and work is key.
3. Journal 1 beautiful life moment
Delicious tacos, presentation you crushed, a moment of inner peace. Write it down.
Gratitude programs a mindset of abundance.
4. Lay out clothes
Get exercise clothes ready for tomorrow. Upon waking up, jump rope for 2 mins. It will activate your mind + body.
You May Also Like
1/“What would need to be true for you to….X”
Why is this the most powerful question you can ask when attempting to reach an agreement with another human being or organization?
A thread, co-written by @deanmbrody:
2/ First, “X” could be lots of things. Examples: What would need to be true for you to
- “Feel it's in our best interest for me to be CMO"
- “Feel that we’re in a good place as a company”
- “Feel that we’re on the same page”
- “Feel that we both got what we wanted from this deal
3/ Normally, we aren’t that direct. Example from startup/VC land:
Founders leave VC meetings thinking that every VC will invest, but they rarely do.
Worse over, the founders don’t know what they need to do in order to be fundable.
4/ So why should you ask the magic Q?
To get clarity.
You want to know where you stand, and what it takes to get what you want in a way that also gets them what they want.
It also holds them (mentally) accountable once the thing they need becomes true.
5/ Staying in the context of soliciting investors, the question is “what would need to be true for you to want to invest (or partner with us on this journey, etc)?”
Multiple responses to this question are likely to deliver a positive result.
Why is this the most powerful question you can ask when attempting to reach an agreement with another human being or organization?
A thread, co-written by @deanmbrody:
Next level tactic when closing a sale, candidate, or investment:
— Erik Torenberg (@eriktorenberg) February 27, 2018
Ask: \u201cWhat needs to be true for you to be all in?\u201d
You'll usually get an explicit answer that you might not get otherwise. It also holds them accountable once the thing they need becomes true.
2/ First, “X” could be lots of things. Examples: What would need to be true for you to
- “Feel it's in our best interest for me to be CMO"
- “Feel that we’re in a good place as a company”
- “Feel that we’re on the same page”
- “Feel that we both got what we wanted from this deal
3/ Normally, we aren’t that direct. Example from startup/VC land:
Founders leave VC meetings thinking that every VC will invest, but they rarely do.
Worse over, the founders don’t know what they need to do in order to be fundable.
4/ So why should you ask the magic Q?
To get clarity.
You want to know where you stand, and what it takes to get what you want in a way that also gets them what they want.
It also holds them (mentally) accountable once the thing they need becomes true.
5/ Staying in the context of soliciting investors, the question is “what would need to be true for you to want to invest (or partner with us on this journey, etc)?”
Multiple responses to this question are likely to deliver a positive result.
