SumIf where sum_range contains text

[Guest]

Hi
The sumif function returns "0" if a cell to be added is a string value
Is there a way to return the string (or error) and not a zero
Cheers
Andrea

 

[Peo Sjoblom]

=IF(SUMPRODUCT(--ISTEXT(B4:B20))>0,"Houston, We Have a Text
Problem",SUMIF(A2:A20,C1,B2:B20))

 

[Guest]

Thanks Peo
but than the whole result column has a text problem, not only the cell concerned
The sum of cells that are not affected (do not contain text) should be added anyway!

----- Peo Sjoblom wrote: ----

=IF(SUMPRODUCT(--ISTEXT(B4:B20))>0,"Houston, We Have a Tex
Problem",SUMIF(A2:A20,C1,B2:B20)

 

[Peo Sjoblom]

What is the problem then?

=SUMIF(A2:A20,C1,B2:B20))

will sum all the numbers in B2:B20 with the criteria in C1 and if there
should be a text string
it won't affect anything? Assume this table


a 1
a 1
b 1
a xxxx
b 1
a 1


assume you have this formula


=SUMIF(A2:A6,"a",B2:B6)

then the above formula will return 3, what would you want it to return?

 

[Guest]

For that I would want the following returns
a xxxx (and not 3!
b
so that I know that a string was entered in the original cell and not a zero
Any idea
Thanks
Andrea

----- Peo Sjoblom wrote: ----

What is the problem then

=SUMIF(A2:A20,C1,B2:B20)

will sum all the numbers in B2:B20 with the criteria in C1 and if ther
should be a text strin
it won't affect anything? Assume this tabl


a
a
b
a xxx
b
a


assume you have this formul


=SUMIF(A2:A6,"a",B2:B6

then the above formula will return 3, what would you want it to return

 

[Peo Sjoblom]

I am sure you misread that, since there are 4 "a" but one has an adjacent
text string it will return 3.
Maybe you could use a filter? Either auto or advanced filter should work, in
the advanced you can filter both for "a" and text string and also filter for
a and number

If there is one text string you can return it like this

=INDEX($B$2:$B$20,MATCH(TRUE,ISTEXT($B$2:$B$20),0))

entered with ctrl + shift & enter


Regards,

Peo Sjoblom