Hi Hivers,
Excel (or spreadsheets in general) is a boon for all. You may be in any profession or business most of us have encountered it and it has proved itself to be effective for all. It has great potentials and very diversified use cases to fit in needs for everyone. It helps in maths, accounts, statistics, analytics, integrity checks, sanity checks, data visualization and what not.
Moreover it can be your first encounter (if not otherwise) with programing.
Two of very basic, very common and very useful functions in excel are sumif and countif. As basic as they sound, but effective for complex, large data sets (within excel sheet range). Sumif can give you sum of all numbers/values in a range 'if they satisfy a condition'. Similarly Countif can give you count of all numbers/values in a range 'if they satisfy a condition'.
Moreover it allows you to give multiple criteria range and multiple criteria (conditions) also use regular expressions for matching. Though it feels pretty basic it turns out to be quite impressive when used in right place for right purpose.
Lets understand the functions with an example.
below is a sample data [downloaded from net]
We might need to get sum of sales for each Region or Person or Item or collectively for a specific item in a specific region by a specific person.
All this can be achieved by a simple formula where we can give range on which we need to perform sum, range on which we need to perform filter based on give condition, and the condition or criteria for which we want to filter out specific data.
So, let the data be in any order the formula picks the cells that matches your filter and adds up their values. As the criteria given in above example is the adjacent cell value, you can edit the cell value and the sum value will instantly change to filter as per cell value.
Though there is a lot more to share, lets wrap this blog here, will come with more such gems, till then keep reading, keep posting, keep learning.
Thanks a lot for reading all through it.