Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change straight quotes to curly quotes | Excel Discussion (Misc queries) | |||
Conditional Formatting question (if cell = 0, wrap cell in quotes) | New Users to Excel | |||
multiply formula where 1 cell has a (sumif) formula as a result | Excel Worksheet Functions | |||
Double quotes in cell and converting to CSV | Excel Discussion (Misc queries) | |||
displaying output of a formula to be in double quotes | Excel Worksheet Functions |