Home |
Search |
Today's Posts |
#1
|
|||
|
|||
text value of a cell
how can I return the text value of a cell useing"sumif" formula
|
#2
|
|||
|
|||
Please share with us where ideas like that come from.
Sumif is, as its name indicates, supposed to return a sum. Sums are almost always numbers. HTH. Best wishes Harald "formula1" skrev i melding ... how can I return the text value of a cell useing"sumif" formula |
#3
|
|||
|
|||
If you mean that you wish to total number values that are text formatted,
where Sumif() doesn't recognize them as numbers, and returns a 0, you could try SumProduct. The *asterisk* form of Sumproduct will recognize text numbers, where as the unary form will not. If A1:A50 are "text" numbers, =SUMPRODUCT((B1:B50="CAT")*A1:A50) *Will* total Column A Where as, =SUMPRODUCT(--(B1:B50="CAT"),A1:A50) Will *not*! -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "formula1" wrote in message ... how can I return the text value of a cell useing"sumif" formula |
#4
|
|||
|
|||
BUT ... I guess it can be coerced, with an *additional* set of unarys:
=SUMPRODUCT(--(B1:B50="CAT"),--A1:A50) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... If you mean that you wish to total number values that are text formatted, where Sumif() doesn't recognize them as numbers, and returns a 0, you could try SumProduct. The *asterisk* form of Sumproduct will recognize text numbers, where as the unary form will not. If A1:A50 are "text" numbers, =SUMPRODUCT((B1:B50="CAT")*A1:A50) *Will* total Column A Where as, =SUMPRODUCT(--(B1:B50="CAT"),A1:A50) Will *not*! -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "formula1" wrote in message ... how can I return the text value of a cell useing"sumif" formula |
#5
|
|||
|
|||
"Ragdyer" wrote in message ... The *asterisk* form of Sumproduct will recognize text numbers, where as the unary form will not. If A1:A50 are "text" numbers, =SUMPRODUCT((B1:B50="CAT")*A1:A50) *Will* total Column A Where as, =SUMPRODUCT(--(B1:B50="CAT"),A1:A50) Will *not*! =SUMPRODUCT(--(B1:B50="CAT"),--(A1:A50)) But I think the OP just wants =SUMPRODUCT(--(A1:A50)) |
#6
|
|||
|
|||
With the "if" in the OP, I do believe that some sort of condition is
supposed to be met. We're both going under the assumption that we understood the OP in the first place. Maybe Harald was correct, and (s)he just misstated the question at the outset.<g -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bob Phillips" wrote in message ... "Ragdyer" wrote in message ... The *asterisk* form of Sumproduct will recognize text numbers, where as the unary form will not. If A1:A50 are "text" numbers, =SUMPRODUCT((B1:B50="CAT")*A1:A50) *Will* total Column A Where as, =SUMPRODUCT(--(B1:B50="CAT"),A1:A50) Will *not*! =SUMPRODUCT(--(B1:B50="CAT"),--(A1:A50)) But I think the OP just wants =SUMPRODUCT(--(A1:A50)) |
#7
|
|||
|
|||
"Ragdyer" wrote in message ... BUT ... I guess it can be coerced, with an *additional* set of unarys: =SUMPRODUCT(--(B1:B50="CAT"),--A1:A50) -- Regards, RD You beat me to it by minutes <g |
#8
|
|||
|
|||
"Ragdyer" wrote in message ... With the "if" in the OP, I do believe that some sort of condition is supposed to be met. See I read that as 'if text'. Good game this. |
#9
|
|||
|
|||
Ragdyer wrote:
[...] The *asterisk* form of Sumproduct will recognize text numbers, where as the unary form will not. If A1:A50 are "text" numbers, =SUMPRODUCT((B1:B50="CAT")*A1:A50) *Will* total Column A Where as, =SUMPRODUCT(--(B1:B50="CAT"),A1:A50) Will *not*! Unary form? You mean of course SumProduct's native comma syntax for the coercer that is needed can be any of +0, *1, ^1, N, or --. How about the comma vs star syntax/form? |
#10
|
|||
|
|||
<<"How about the comma vs star syntax/form?"
I'm missing your point? -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "Aladin Akyurek" wrote in message ... Ragdyer wrote: [...] The *asterisk* form of Sumproduct will recognize text numbers, where as the unary form will not. If A1:A50 are "text" numbers, =SUMPRODUCT((B1:B50="CAT")*A1:A50) *Will* total Column A Where as, =SUMPRODUCT(--(B1:B50="CAT"),A1:A50) Will *not*! Unary form? You mean of course SumProduct's native comma syntax for the coercer that is needed can be any of +0, *1, ^1, N, or --. How about the comma vs star syntax/form? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to auto-fill text based on text in another cell | Excel Discussion (Misc queries) | |||
Text in cell not showing in full | Excel Discussion (Misc queries) | |||
Moving text from one cell to another. | Excel Worksheet Functions | |||
Forcing text onto new line in cell | New Users to Excel | |||
want if cell containts text, then increase count by one | Excel Worksheet Functions |