Excel Functions - SumIF AND Combined
Apr 12, 2008
by
0
Maybe you'll laugh now that you know I just switched from Microsoft Office Professional 97 (wince - don't throw tomatoes) to the new 2007 Office Suite. Literally within minutes I was loving the new features of Word and especially Excel. We build a lot of websites, but spreadsheets come into play a lot when prepping/scrubbing data for database insertion or just practical business applications.
There are actually some new functions that make spread sheeting less of a chore and actually a bit enjoyable.
One of the struggles I had recently was using the SUMIF function to calculate sums of data based on criteria in a range. SUMIF only will compare one range of data.
For example, if you have a column of fruit and a column of vegetables and you want to add the price of a 3rd column based on whether the fruit column contains "bananas" you would use this formula.
Using =SUMIF(A3:A12,"banana",C3:C12) the criteria is compared and the correct sum of rows containing "banana" is calculated.
But what if you want to add only rows where the fruit is "banana" AND the vegetable is "brocolli". Well in the past you'd have to get a little creative with COUNTIF and comparing numbers on another range calculation OR use SUMPRODUCT - which is not a best use for this particular situation.
Now with SUMIFS you can do it in one nice clean function.
=SUMIFS(C3:C12,A3:A12,"banana",B3:B12,"brocolli")
You can see how simple it is. You can also continue with several column criteria comparison if you like.
Nice work Microsoft - I have to give it to them on this one.
Leave a comment
To leave a comment, please log in / sign up
RSS Feed
Facebook
Twitter
LinkedIn
YouTube