| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using " " in If functions
Hi I have 2 If functions: =SUMIF(C3:C7;TRUE;B3:B7) and =COUNTIF(B2:I2;"=70") Although I understand the equation behind it I always get confused as to when I should use the " " . As from my examples one of they are required only in one of these functions. Could someone please make it easier for me to remember when any why they should be used. Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using " " in If functions
Double quotes is used to refer anything which is not a cell reference OR a
numeric value.. For Text strings, ,=< etc; you need to enclose within quotes. So "=70" is same as "=" & 70 (since 70 is numeric) '&' combines the text string within quotes with the numeric value.. If this post helps click Yes --------------- Jacob Skaria "Cazzy123" wrote: Hi I have 2 If functions: =SUMIF(C3:C7;TRUE;B3:B7) and =COUNTIF(B2:I2;"=70") Although I understand the equation behind it I always get confused as to when I should use the " " . As from my examples one of they are required only in one of these functions. Could someone please make it easier for me to remember when any why they should be used. Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using " " in If functions
Yes this is what I thought Jacob but surely the word TRUE in the SumIF is
classed as text? Caroline "Jacob Skaria" wrote: Double quotes is used to refer anything which is not a cell reference OR a numeric value.. For Text strings, ,=< etc; you need to enclose within quotes. So "=70" is same as "=" & 70 (since 70 is numeric) '&' combines the text string within quotes with the numeric value.. If this post helps click Yes --------------- Jacob Skaria "Cazzy123" wrote: Hi I have 2 If functions: =SUMIF(C3:C7;TRUE;B3:B7) and =COUNTIF(B2:I2;"=70") Although I understand the equation behind it I always get confused as to when I should use the " " . As from my examples one of they are required only in one of these functions. Could someone please make it easier for me to remember when any why they should be used. Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using " " in If functions
TRUE and FALSE are keywords which do not require quotes...Try some other text..
If this post helps click Yes --------------- Jacob Skaria "Cazzy123" wrote: Yes this is what I thought Jacob but surely the word TRUE in the SumIF is classed as text? Caroline "Jacob Skaria" wrote: Double quotes is used to refer anything which is not a cell reference OR a numeric value.. For Text strings, ,=< etc; you need to enclose within quotes. So "=70" is same as "=" & 70 (since 70 is numeric) '&' combines the text string within quotes with the numeric value.. If this post helps click Yes --------------- Jacob Skaria "Cazzy123" wrote: Hi I have 2 If functions: =SUMIF(C3:C7;TRUE;B3:B7) and =COUNTIF(B2:I2;"=70") Although I understand the equation behind it I always get confused as to when I should use the " " . As from my examples one of they are required only in one of these functions. Could someone please make it easier for me to remember when any why they should be used. Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using " " in If functions
=SUMIF(C3:C7;TRUE;B3:B7)
Ready for some confusion? Suppose you import data from another source and some of that data is the *TEXT* value TRUE: TRUE...1 TRUE...1 =SUMIF(A1:A2,TRUE,B1:B2) = 0 =SUMIF(A1:A2,"TRUE",B1:B2) = 0 WTH! =SUMIF(A1:A2,"*TRUE",B1:B2) = 2 You have to coerce SUMIF to explicitly evaluate TRUE as a text value. The same thing happens with COUNTIF: =COUNTIF(A1:A2,TRUE) = 0 =COUNTIF(A1:A2,"TRUE") = 0 =COUNTIF(A1:A2,"*TRUE") = 2 -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... TRUE and FALSE are keywords which do not require quotes...Try some other text.. If this post helps click Yes --------------- Jacob Skaria "Cazzy123" wrote: Yes this is what I thought Jacob but surely the word TRUE in the SumIF is classed as text? Caroline "Jacob Skaria" wrote: Double quotes is used to refer anything which is not a cell reference OR a numeric value.. For Text strings, ,=< etc; you need to enclose within quotes. So "=70" is same as "=" & 70 (since 70 is numeric) '&' combines the text string within quotes with the numeric value.. If this post helps click Yes --------------- Jacob Skaria "Cazzy123" wrote: Hi I have 2 If functions: =SUMIF(C3:C7;TRUE;B3:B7) and =COUNTIF(B2:I2;"=70") Although I understand the equation behind it I always get confused as to when I should use the " " . As from my examples one of they are required only in one of these functions. Could someone please make it easier for me to remember when any why they should be used. Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using " " in If functions
Hi,
Since = is text you need quote around it however you can enter the =COUNTIF(B2:I2;"=70") =COUNTIF(B2:I2,"="&A1) In this case A1 would contain 70 In this second case the text portion of the entry is quoted but the cell reference is not or else it would be treated as text. This form gives you more flexibility - you don't need to modify formulas to change criteria you just edit the contents of A1. Here is an example of counting the number of entries equal or above the average =COUNTIF(A1:A14,"="&AVERAGE(A1:A14)) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Cazzy123" wrote: Hi I have 2 If functions: =SUMIF(C3:C7;TRUE;B3:B7) and =COUNTIF(B2:I2;"=70") Although I understand the equation behind it I always get confused as to when I should use the " " . As from my examples one of they are required only in one of these functions. Could someone please make it easier for me to remember when any why they should be used. Thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using " " in If functions
Hi,
SUMIF(A1:A2,TRUE,B1:B2) SUMIF(A1:A2,"TRUE",B1:B2) For me both the functions evaluate to 2. I just typed TRUE in cell A1 and A2. Am I missing something -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "T. Valko" wrote in message ... =SUMIF(C3:C7;TRUE;B3:B7) Ready for some confusion? Suppose you import data from another source and some of that data is the *TEXT* value TRUE: TRUE...1 TRUE...1 =SUMIF(A1:A2,TRUE,B1:B2) = 0 =SUMIF(A1:A2,"TRUE",B1:B2) = 0 WTH! =SUMIF(A1:A2,"*TRUE",B1:B2) = 2 You have to coerce SUMIF to explicitly evaluate TRUE as a text value. The same thing happens with COUNTIF: =COUNTIF(A1:A2,TRUE) = 0 =COUNTIF(A1:A2,"TRUE") = 0 =COUNTIF(A1:A2,"*TRUE") = 2 -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... TRUE and FALSE are keywords which do not require quotes...Try some other text.. If this post helps click Yes --------------- Jacob Skaria "Cazzy123" wrote: Yes this is what I thought Jacob but surely the word TRUE in the SumIF is classed as text? Caroline "Jacob Skaria" wrote: Double quotes is used to refer anything which is not a cell reference OR a numeric value.. For Text strings, ,=< etc; you need to enclose within quotes. So "=70" is same as "=" & 70 (since 70 is numeric) '&' combines the text string within quotes with the numeric value.. If this post helps click Yes --------------- Jacob Skaria "Cazzy123" wrote: Hi I have 2 If functions: =SUMIF(C3:C7;TRUE;B3:B7) and =COUNTIF(B2:I2;"=70") Although I understand the equation behind it I always get confused as to when I should use the " " . As from my examples one of they are required only in one of these functions. Could someone please make it easier for me to remember when any why they should be used. Thanks |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using " " in If functions
Don't enter boolean TRUE, enter *text* true.
-- Biff Microsoft Excel MVP "Ashish Mathur" wrote in message ... Hi, SUMIF(A1:A2,TRUE,B1:B2) SUMIF(A1:A2,"TRUE",B1:B2) For me both the functions evaluate to 2. I just typed TRUE in cell A1 and A2. Am I missing something -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "T. Valko" wrote in message ... =SUMIF(C3:C7;TRUE;B3:B7) Ready for some confusion? Suppose you import data from another source and some of that data is the *TEXT* value TRUE: TRUE...1 TRUE...1 =SUMIF(A1:A2,TRUE,B1:B2) = 0 =SUMIF(A1:A2,"TRUE",B1:B2) = 0 WTH! =SUMIF(A1:A2,"*TRUE",B1:B2) = 2 You have to coerce SUMIF to explicitly evaluate TRUE as a text value. The same thing happens with COUNTIF: =COUNTIF(A1:A2,TRUE) = 0 =COUNTIF(A1:A2,"TRUE") = 0 =COUNTIF(A1:A2,"*TRUE") = 2 -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... TRUE and FALSE are keywords which do not require quotes...Try some other text.. If this post helps click Yes --------------- Jacob Skaria "Cazzy123" wrote: Yes this is what I thought Jacob but surely the word TRUE in the SumIF is classed as text? Caroline "Jacob Skaria" wrote: Double quotes is used to refer anything which is not a cell reference OR a numeric value.. For Text strings, ,=< etc; you need to enclose within quotes. So "=70" is same as "=" & 70 (since 70 is numeric) '&' combines the text string within quotes with the numeric value.. If this post helps click Yes --------------- Jacob Skaria "Cazzy123" wrote: Hi I have 2 If functions: =SUMIF(C3:C7;TRUE;B3:B7) and =COUNTIF(B2:I2;"=70") Although I understand the equation behind it I always get confused as to when I should use the " " . As from my examples one of they are required only in one of these functions. Could someone please make it easier for me to remember when any why they should be used. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| can I nest COUNTIF functions(C5:C8,"apples") AND (D5:D8,"green") | Excel Worksheet Functions | |||
| Functions for "current" & "previous" month to calculate data | Excel Worksheet Functions | |||
| Excel "Move or Copy" and "Delete" sheet functions | Excel Worksheet Functions | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| can we convert "2 days 16 hrs" to " 64hrs" using excel functions | Excel Worksheet Functions | |||
Linear Mode
