sumif for Text cells? What works?

**Jirime** · 01-09-2009, 03:28 PM

Good afternoon, I am currently trying to do the same function as =sumif(range,”Value”,range) for cells that contain text and have the text show as the value in the cell. Any help would be greatly appreciated, thanks

**Ron Coderre** · 01-09-2009, 03:31 PM

Since SUMIF returns numeric values....Can you post some sample data, what you want the formula to look for, and what value the formula should return?

**protonLeah** · 01-09-2009, 03:34 PM

That's very vague. Please attach a sample wrkbk or, at least describe the layout of the sheet. I.e., the cells/contents being examined and what you need from the formula.

**Jirime** · 01-09-2009, 03:36 PM

I would like for it to look for "C" and return the data that is entered in the Cell, which will be an "Project Name", ie Excel Help Forum Building A.

**daddylonglegs** · 01-09-2009, 03:41 PM

Are you expecting to return a value from a single cell? This seems like a job for a lookup function rather than SUMIF. If you want to search for "C" in A1:A10 and return the corresponding value from B1:B10 try

=VLOOKUP("C",A1:B10,2,0)

**Ron Coderre** · 01-09-2009, 03:42 PM

Originally Posted by Jirime

I would like for it to look for "C" and return the data that is entered in the Cell, which will be an "Project Name", ie Excel Help Forum Building A.

It sounds like you need a lookup formula.

Example:
With this list in A1:B3

Please Login or Register  to view this content.

and
D1: C

This formula returns the Project Name associated with the value in D1

Please Login or Register  to view this content.

In the above example, the formula returns: New Project.

Is that something you can work with?

**Jirime** · 01-09-2009, 03:51 PM

Workbook layout is 45 individual sheets I am taking the values both numeric and alpha from 13 separate sheets to compile in to one sheet, I found that I can use the sum if for the numeric values for both ranges and single cells, to transfer the date by using =SUMIF('2008'!E5,"C",'2008'!P5), Yet I also need to sort the alpha values the same way to only be captured if it is “C”. I am not sure if I am explaining it correctly, but hopefully this will explain a bit better.

**Jirime** · 01-09-2009, 03:53 PM

Originally Posted by Ron Coderre

It sounds like you need a lookup formula.

Example:
With this list in A1:B3

Please Login or Register  to view this content.

and
D1: C

This formula returns the Project Name associated with the value in D1

Please Login or Register  to view this content.

In the above example, the formula returns: New Project.

Is that something you can work with?

Will this give me only the one that are in Column A that meet the stated criteria of "C"

**daddylonglegs** · 01-09-2009, 04:29 PM

If you are only looking at a single cell perhaps you should use an IF formula, i.e.

=IF('2008'!E5="C",'2008'!P5,"")

This will work for both numeric and text values

**protonLeah** · 01-09-2009, 04:41 PM

Still confused.
1. You said:

Workbook layout is 45 individual sheets...

It would help much if you would post a single sheet with representative data.
2. and:

...I found that I can use the sumif for the numeric values for both ranges and single cells, to transfer the date by using =SUMIF('2008'!E5,"C",'2008'!P5)...

SUMIF returns a "CONDITIONAL TOTAL". So, what do you mean when you say that you "transferred" data?
3. finally:

...I also need to sort (??) the alpha values the same way to only be captured if it is “C”....

Again, a workbook with, say, 25 - 30 rows of sample data (both "C" and "non-C") will help greatly.

**Jirime** · 01-09-2009, 04:42 PM

Originally Posted by daddylonglegs

If you are only looking at a single cell perhaps you should use an IF formula, i.e.

=IF('2008'!E5="C",'2008'!P5,"")

This will work for both numeric and text values

THANKS!!!!!! This work perfectly!!!!