• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

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
0
Petersburg1
Asked:
Petersburg1
3 Solutions
 
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now