Question in regards of SUMIF formula

Dear Experts,
I'm using the following formula which works as I want:
=SUMIF(A:A;">="&F1;B:B)-SUMIF(A:A;">="&F1+G1;B:B)

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. :-)
thanks
Petersburg1Asked:
Who is Participating?
 
Michael FowlerSolutions ConsultantCommented:
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

=SUMIF(A:A;">=2";B:B)-SUMIF(A:A;">=2"+G1;B:B)

Hope this helps

Michael
0
 
dlmilleCommented:
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?

Dave
0
 
RamanhpCommented:
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 ,
however,
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




0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Michael FowlerSolutions ConsultantCommented:
@Ramanhp

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.

Michael
0
 
Rory ArchibaldCommented:
I think what Raman means is that using:
=SUMIF(A:A;">=F1";B:B)
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:
=SUMIF(A:A;F1;B:B)
rather than
=SUMIF(A:A;"="&F1;B:B)
0
 
Petersburg1Author Commented:
thank you very much to all of you.
It was helfull.
thank
Nils
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.