sumif

SUMIF Function

Many a times, we require a sum of an array or range with certain criteria’s to be filtered.

Lets understand the formula first. 

=SUMIF(Range,Criteria,[Sum range])

Range: List of items where criteria needs to be filtered

Criteria: Used to filter from the main array or range (E.g. “Apple”,>100)

Sumrange: Range/Array, whose sum you require.

Lets us see the video, which will help you to get some basics of SUMIF function.

Remember: While insert criteria as a Name or character kindly insert within “ ” quotes

Optimize Excel sheet calculation

If you’re in a position where excel has slowly become a tool, or really a programming api in itself, you are likely about as excited about hitting the save or calculate buttons as you would be to hear your dog ate your ipad.

Likely, you’ve trolled googled and are well versed in VLOOKUP, Sumif, Sumproduct, Array formulas, and countifs..

If you have excel 2007 and up, there is a light at the end of the tunnel. Excel 2007+ contains some new functions, including SUMIFS and COUNTIFS!

Remember why you had to use array formulas (that look like {=(Sum…  )to begin with? Because countif on it’s own couldn’t handle multiple conditions.

So if you do counts or sums across values and want multiple conditions Replace your array formulas sums and counts with SUMIFS and COUNTIFS

Ex: Sum all values in ColA where ColB=Georgia and ColC=Yes
translates to =SUMIFS(A:A,B:B,“Georgia”,C:C,“Yes”)

Ex2: count all occurences where ColB=Pennsylvania and ColD=Booked
translates to =COUNTIFS(B:B,“Pennsylvania”,D:D,“Booked”)

Simple huh?

It get’s better…you can get rid of those pesky named ranges you had to create for your array formulas to work..

You’re welcome.

Credit my awareness of these new functions to http://msdn.microsoft.com/en-us/library/aa730921.aspx

Ok OK I’m not done yet..
So you hate those #N/A’s but even more you hate typing out..if(ISNA(Vlookup……

I do too.

You can start using the IFERROR function instead of IF(ISNA(vlookup in some cases!!
You do not need to set a true condition for IFERROR, only a false condition.

The true condition will return the value of the formula.

Ex.

If(ISNA(Vlookup(G1,‘OtherSheet’!D:D,1,FALSE)),“No”,Vlookup(G1,'OtherSheet’!D:D,1,FALSE))
changes to

Iferror(Vlookup(G1,'OtherSheet’!D:D,1,FALSE),“No”)

How to use the aggregate function in R like a sumif in Excel

> data
 a b
1 0 a
2 0 a
3 1 b
4 2 a
5 2 b
6 2 a
7 3 b
8 4 b
9 7 b
> aggregate(data$a, by = list(Category = data$b), FUN = sum)
 Category  x
1        a  4
2        b 17


Slightly longer explanation at Tom’s blog

youtube

Learn how SUMIF function works!!!

Purposely the video was created with no volume, in order to make it simpler. If you feel that I should change the format or add the volume, please submit your thoughts

SumIfs, Dropdowns Part 2

Here’s a system of equations I use on spreadsheets to get a dynamic table that changes sums as you change criterias.

In your source data, create a “Line ID” column as your left-most column that concatenates a few data elements that describe your line of data.

For example, for an invoice your company has outstanding from a customer, it might be:

=‘Customer’&'Month of Invoice’ (with appropriate cell references)

Don’t put the Invoice Balance or Invoice Amount in there; that’s the data you’re looking to describe.

Then create a list with all of your customers on a seperate tab, name the range as “Customers” (select all of the customer names and type in the cell reference box in the upper left corner).

Do the same for months and label it “Months.”

Put two cells in a spreadsheet and choose Data Validation, and choose to allow from a List, then type “=Customers” and “=Months” for the respective cells. This will give you dropdowns to choose customer names and months from.

Next you can type a SumIf in the cell below them. The range would be the first row from the data with the concatenation in it (the one you typed ='Customer’&'Month of Invoice’ into), the criteria would be a concatenation of values in the two dropdowns you just created ('Customers’&'Months’), and the sum range would be the amount you want to sum (let’s say Invoice Balance).

By choosing different values from the dropdown list, you can now get the total invoice balance for any customer in any one month. Another (relatively) simple yet powerful report.

#ERROR! ... o.O

Computer applications can definitely be difficult to use especially when introduced with so many different tools for each application. Microsoft Excel really takes home the award. Although I normally use Excel for work on a daily basis, I honestly had no idea you could do so much with it. One of my favorite things about Excel are all the different types of functions you can use. (I still can’t believe there was a time where all I thought you can do was simple calculations - boy was I wrong!)

My TOP TWO FAVORITE Excel functions are:

#1

SumIf()

 Sum() works great if you want to add everything in sight, but what if we want to add values based on a specific criteria?

 Enter SumIf(), which will sum a given range of values, if a given criteria is met. In pseudo code, SumIf() reads as =SumIf(Range of Criteria Values, Your Criteria, Range to be Summed). The last argument, separated by commas, is optional – if omitted, the Range of Criteria Values is summed.


#2

CountIf()

Like SumIf(), CountIf() provides us with a manner of conditional counting. The slight variance is that it takes two arguments, the range you want to evaluate and the criteria to evaluate that range by, e.g., =CountIf(Range you want to count, Criteria to be met).

SumIfs, Dropdowns Part 1

Let’s say you have a table of data showing invoices your company has outstanding with their customers. It includes rows of Customer Name, Customer Info (like address and the like), Invoice Creation Month, Invoice Amount (Amount Charged) and current Invoice Balance.

You can create a simple but versatile report using just a few steps. Create a list of all your customers, select the entire list and name the range “Customers” (select all of the customer names and type in the cell reference box in the upper left corner).

Create a dropdown by using the Data Validation in one cell and choose to allow from a List and type “=Customers”. This will create a dropdown with all of your customer names on it.

Create a SumIf, choosing the Customer Name column as your Range, the dropdown as your criteria, and, for this example, Invoice Balance.

Then you can see the outstanding balance for any customer by choosing them from the dropdown. Simple, but powerful.