Possibly one of the most powerful easy to use formulas in Excel is the SUMIF formula.
It is very easy to use once you get your head around its syntax and it is extremely useful in all sorts of scenarios.
Here is the Microsoft Excel Explanation.
You use the SUMIF function to sum the values in a range (Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) that meet criteria that you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 5.
Syntax SUMIF(range, criteria, [sum_range])
I will explain that below more simply.
First let me explain some common scenarios where you might use SUMIF. Hopefully from there you can envisage similar scenarios for your own circumstances.
You have a list of names and values – maybe pays or scores or totals of times you beat them in darts matches or numbers of volunteers in each division or county. When the names appear more than once – e.g. you might have several different staff categories in each store across each state as per the example below.
Lets say you want to sum the total number of Part Time Employees in South Australia across all stores.
I am going to show you a summary table below this one that provides all the results for each staff category for each State and then explain how this was done using one simple formula. An understanding of ABSOLUTE and RELATIVE references is important.
Time for a link dump from my browser – clearing out tabs.
How to Create a Portfolio with Evernote (Education Series) – Evernote is an awesome app – We use it all the time on various android and windows platforms.
Building trust - The Connection Constant
Want to build a online store? Choosing a payment gateway
Facebook – how to convert your personal account into a Facebook Page - for all those people who created a “person” for their business page.
More how to convert Facebook profiles into business pages including tricks and traps
Configuring and adding images to Drupal 7 content with Wysiwyg, IMCE and Lightbox2 – very good tutorial
Probably the best explanation of using INDEX function for non volatile dynamic range creation in Excel
There will be lots coming on Windows 8 on this blog later – for now heres a very good read about how Windows 8 can pool data storage
Realities of Business is a new website by a great friend to promote his new book. Worth a read! Buy it now!
Two local photographers worth checking out.
The AND function takes each of the conditions inside the brackets and evaluates for their truth, and then multiplies the results together. TRUE = 1 and FALSE = 0. So if any condition is FALSE then the overall statement returns FALSE.
The formula for AND needs to be placed in brackets with each of the conditions separated by a comma. For example =AND(H1<1,G1>1)
In this instance the function will evaluate H1 to see if the value is less than 1. if it is then it will return TRUE or 1 and if not FALSE or 0. Then it will do the same for G1.
The two values are multiplied together and the answer is then either 1 or 0, TRUE or FALSE.
The Excel Help says:
One common use for the AND function is to expand the usefulness of other functions that perform logical tests. For example, the IF function performs a logical test and then returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE. By using the AND function as the logical_test argument of the IF function, you can test many different conditions instead of just one.
I had an interesting question about conditional formatting posed in the comments by Stephen.
In a new sheet, I am trying to make a whole row turn red, green or amber depending on the value of one cell in that row, so I can easily see which jobs we have won, lost or are pending. Any 'IF' conditional formula I write gets thrown out by Excel. What am I doing wrong?
I promised him an answer so here it is.
For this exercise I am making some assumptions.
Recent comments
10 years 37 weeks ago
10 years 37 weeks ago
10 years 39 weeks ago
10 years 39 weeks ago
10 years 39 weeks ago
10 years 39 weeks ago
10 years 39 weeks ago
10 years 39 weeks ago
10 years 39 weeks ago
10 years 39 weeks ago