G
Guest
how can I return the text value of a cell useing"sumif" formula
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
H
Harald Staff
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
Sumif is, as its name indicates, supposed to return a sum. Sums are almost
always numbers.
HTH. Best wishes Harald
R
Ragdyer
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*!
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*!
R
Ragdyer
BUT ... I guess it can be coerced, with an *additional* set of unarys:
=SUMPRODUCT(--(B1:B50="CAT"),--A1:A50)
=SUMPRODUCT(--(B1:B50="CAT"),--A1:A50)
B
Bob Phillips
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))
R
Ragdyer
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>
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>
B
Bob Phillips
Ragdyer said:BUT ... I guess it can be coerced, with an *additional* set of unarys:
=SUMPRODUCT(--(B1:B50="CAT"),--A1:A50)
You beat me to it by minutes <g>
B
Bob Phillips
Ragdyer said: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.
A
Aladin Akyurek
Ragdyer wrote:
[...]
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?
[...]
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?
R
RagDyeR
<<"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 !
--------------------------------------------------------------------
Ragdyer wrote:
[...]
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?
I'm missing your point?
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------
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?
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.