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
The entire discussion around Facebook’s disclosures of what happened in 2016 is very frustrating. No exec stopped any investigations, but there were a lot of heated discussions about what to publish and when.
In the spring and summer of 2016, as reported by the Times, activity we traced to GRU was reported to the FBI. This was the standard model of interaction companies used for nation-state attacks against likely US targeted.
In the Spring of 2017, after a deep dive into the Fake News phenomena, the security team wanted to publish an update that covered what we had learned. At this point, we didn’t have any advertising content or the big IRA cluster, but we did know about the GRU model.
This report when through dozens of edits as different equities were represented. I did not have any meetings with Sheryl on the paper, but I can’t speak to whether she was in the loop with my higher-ups.
In the end, the difficult question of attribution was settled by us pointing to the DNI report instead of saying Russia or GRU directly. In my pre-briefs with members of Congress, I made it clear that we believed this action was GRU.
The story doesn\u2019t say you were told not to... it says you did so without approval and they tried to obfuscate what you found. Is that true?
— Sarah Frier (@sarahfrier) November 15, 2018
In the spring and summer of 2016, as reported by the Times, activity we traced to GRU was reported to the FBI. This was the standard model of interaction companies used for nation-state attacks against likely US targeted.
In the Spring of 2017, after a deep dive into the Fake News phenomena, the security team wanted to publish an update that covered what we had learned. At this point, we didn’t have any advertising content or the big IRA cluster, but we did know about the GRU model.
This report when through dozens of edits as different equities were represented. I did not have any meetings with Sheryl on the paper, but I can’t speak to whether she was in the loop with my higher-ups.
In the end, the difficult question of attribution was settled by us pointing to the DNI report instead of saying Russia or GRU directly. In my pre-briefs with members of Congress, I made it clear that we believed this action was GRU.
