Where to put quotes in a SUMIF formula and when is & used for cell
 

I wrote a SUMIF formula as follows and got an error
=SUMIF(C5:C10,=25,D5:D10)

but it worked when I put the quotes in the criteria like so:
=SUMIF(C5:C10,"=25",D5:D10)

Q: When do I use the quotes?
.................................................. .................................................. ......

It gets more confusing when I use a cell reference for the value 25 in the
criteria.

=SUMIF(C5:C10,"=B5",D5:D10) does not work - returns a value 0

=SUMIF(C5:C10,"="B5,D5:D10) Gives and error message

However
=SUMIF(C5:C10,"="&B5,D5:D10) works.

Q: Where do we put the quotes and what does the & infront of B5 do to make
it work?

I tired looking up & in the help menu but cant seem to find any references.

Where to put quotes in a SUMIF formula and when is & used for cell
 

When the argument is not a simple value or cell reference
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Blubber" wrote in message
...
I wrote a SUMIF formula as follows and got an error
=SUMIF(C5:C10,=25,D5:D10)

but it worked when I put the quotes in the criteria like so:
=SUMIF(C5:C10,"=25",D5:D10)

Q: When do I use the quotes?
.................................................. .................................................. .....

It gets more confusing when I use a cell reference for the value 25 in the
criteria.

=SUMIF(C5:C10,"=B5",D5:D10) does not work - returns a value 0

=SUMIF(C5:C10,"="B5,D5:D10) Gives and error message

However
=SUMIF(C5:C10,"="&B5,D5:D10) works.

Q: Where do we put the quotes and what does the & infront of B5 do to make
it work?

I tired looking up & in the help menu but cant seem to find any
references.

Where to put quotes in a SUMIF formula and when is & used for cell
 

Hi,

- Comparison operators require quotes: , <, <, =, <=
- You can include a value within the quote but not a cell reference, so
"10" is ok but "A1" is not, if A1 is a cell address. Everything within
quote is static, not dynamic, so if you need it to be dynamic you need to put
it outside the quotes, but in that case you need to combine the operator with
the dynamic stuff using the &.
- The & sign is the concatenation operator. You need to use this if you are
using a comparison operator with a cell reference or formula, for example
"="&A1 or "<="&MIN(A1:A10)



--
Thanks,
Shane Devenshire


"Blubber" wrote:

I wrote a SUMIF formula as follows and got an error
=SUMIF(C5:C10,=25,D5:D10)

but it worked when I put the quotes in the criteria like so:
=SUMIF(C5:C10,"=25",D5:D10)

Q: When do I use the quotes?
.................................................. .................................................. .....

It gets more confusing when I use a cell reference for the value 25 in the
criteria.

=SUMIF(C5:C10,"=B5",D5:D10) does not work - returns a value 0

=SUMIF(C5:C10,"="B5,D5:D10) Gives and error message

However
=SUMIF(C5:C10,"="&B5,D5:D10) works.

Q: Where do we put the quotes and what does the & infront of B5 do to make
it work?

I tired looking up & in the help menu but cant seem to find any references.