Question in regards of SUMIF formula

Posted on 2011-10-12
Last Modified: 2012-05-12
Dear Experts,
I'm using the following formula which works as I want:

Someone could be explain why I need the "&" sign here and what it does?
I understood that the formula does not work without it...but I do not understand why it works with it. :-)
Question by:Petersburg1
    LVL 23

    Accepted Solution

    The & symbol adds the contents of F1 to the test variable

    The test variable is a string so to combine the contents of the cell F1 to the >= you use the & symbol

    If you wanted to place the contents of F1 + the word test in a cell you would use =F1 & "test"

    If you want to sumif on each cell greater than or equal to 2 the formula would be


    Hope this helps

    LVL 41

    Assisted Solution

    The & sign concatenates a string with another.

    In this instance, if F1 equaled  5, then:

    ">=" & F1 would be equivalent to ">=5"

    and ">=" & F1+G1, if F1 were 5 and G1 were 6, then

    ">=" & F1+G1 would be equivalent to ">=11"

    Make sense?

    LVL 9

    Assisted Solution

    In simple words you are using a value in the conditional statement which is coming from cells, in this case,  F1 and G1 ,

    when you say "F1" it refers it as text ,
    when you say F1, it will reference out the value from the cell F1, and that could be any stored value therein,
    therefore, we use  "&" sign to differentiate the two

    LVL 23

    Expert Comment


    The & symbol is joining two text values ie concatenating them.

    You differentiate between the text "F1" and the cell F1 by using double quotes as you did in your post.

    LVL 85

    Expert Comment

    by:Rory Archibald
    I think what Raman means is that using:
    would treat F1 as literal text - i.e. "F1" rather than a cell reference - hence the need to concatenate.

    As an aside, and not for points, this is only necessary as it is not a straight equality comparison, in which case you don't need to explicitly use "=" and can just use:
    rather than

    Author Closing Comment

    thank you very much to all of you.
    It was helfull.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now