One of the questions that we have been receiving quite a bit in recent months has been regarding the difference between the Sumif and Vlookup functions in excel. They are fairly different but there is some confusion regarding their use. Let’s start off by explaining both:

–Sumif function: Is used to sum the quantity (or any other data in a separate column) for a specific criteria. We already wrote about this function, you can find out more about the sumif and countif functions
–Vlookup function: Is used to find a reference associated to a specific criteria

These are the formulas used:

=SUMIF(A:A,”Strawberry”,B:B)
=VLOOKUP(“Strawberry”,A:B,2,FALSE)

When do you use each one?

Vlookup

-If you only have one sample for each “fruit”
-If the data you are looking for is not a number
-If you want to easily spot a “missing fruit” – vlookup will give you a “N/A” result when a vlookup returns no results

Sumif

-If you have more than one sample
-If you want to avoid getting “N/A” errors that will screw up calculations in your spreadsheet

Does that explain it well enough?

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss

***************************************************

Posted on 17 August 2011 by Leslie Currently, Comments and Pings are both off.

9 Feedbacks on "Using Excel Sumif or Vlookup Functions"

mehra

How could we use vlookup and sumif if we have the same refference 3 or 4 time and want to find out with vlookup and it comes as it added the whole same refference in 1 cell plz help me out if we can

19 April 2012 at 2:50

ata

i would like to know how to write a formula to help me find out about our hotel stay over guests. I will have arrival list and departure list two columns with names i would like to see which names are match and if they are i would like to see in the cell next to arrival name cell stay over written

10 May 2012 at 0:23

dayanand patil

very nice thanks to your all executive…

05 July 2012 at 23:51

dinesh

07 July 2012 at 5:38

vijay

The thing is verymuch useful for the beginners to explore excel.

16 July 2012 at 22:00

Petros

Do you want to read about the syntax and usage of an Excel 2013 or VBA function in your native language and practice with a demo workbook ?

The facts:
There are over 600 Excel & VBA functions in Office 2013.
Excel functions have been translated in 16 languages.
Microsoft offers over 20,000+ function help webpages in 50+ languages.

How to navigate fast among so many help pages ?

This free Ribbon Add-in will help you navigate to Microsoft’s online help pages with embedded workbooks, which can be downloaded to your computer for function inspection and in-depth practice.

http://www.spreadsheet1.com/excel-2013-translated-functions-free-addins.html

08 February 2014 at 20:45