(FORMULA) Countif the cell contains the text from another cell

**yhyhyhyh** · 10-20-2014, 11:45 PM

Hi everyone

I'm relatively new to excel and i am facing a problem:

In sheet 1-
cell A1 contains "APPLE & PIE"
cell A2 contains " APPLE & PIE"
Cell A3 contains "APPLE & PIE "

In sheet 2-
cell A1 contains " APPLE & PIE " ([space] APPLE & PIE [Space])
i would like to count the number of cells containing the text in sheet 2's A1. i've tried "COUNTIF" formula but the answer shows 0 due to the difference in the formatting of the texts in cells A1-A3(some with spaces in front, while some with spaces behind).

Is there a way to go about doing this?

Thank you in advance!

**FDibbins** · 10-20-2014, 11:52 PM

Hi, welcome to the forum

From the sounds of it, you have Apple Pie repeated in sheet2 cells, but only once in sheet1 cells? If so, then if you base your coung on sheet2 (with double the text), and try and count the matches in sheet1, you will get 0 because excel is trying to match the exact contents of sheet2 with the exact contents of the cells in sheet1, and of course, there is no exact match.

hmm wait, maybe I read that wrong, and you were trying to show the spaces?
If so, try using the TRIM() command....
=COUNTIF(Sheet1!$A$1:$A$7,trim(A1))

**boopathiraja** · 10-20-2014, 11:53 PM

Hi, One way

Formula:

Please Login or Register  to view this content.

This can be used for irregular spaces, basically it will take out all the spaces & count

**boopathiraja** · 10-20-2014, 11:54 PM

Hi, Ref the attachment

**yhyhyhyh** · 10-21-2014, 12:17 AM

Hi there!

Thanks so much for the prompt reply, your formula worked GREAT

Thank you so so much!

(FORMULA) Countif the cell contains the text from another cell

LinkBack

Thread Tools

Rate This Thread

Display

(FORMULA) Countif the cell contains the text from another cell

Re: (FORMULA) Countif the cell contains the text from another cell

Re: (FORMULA) Countif the cell contains the text from another cell

Re: (FORMULA) Countif the cell contains the text from another cell

Re: (FORMULA) Countif the cell contains the text from another cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Count # of times text appears in cell range by cell referencing not =countif(A:A, ="text")

[SOLVED] Enter formula result from one cell into a COUNTIF formula in another cell. Help please!

[SOLVED] countif formula changes cell values and cell reference is unchanged

Formula to identify part of a text in cell from range of cells & insert adjacent cell text

[SOLVED] Countif cell contains this text string, cell does not have to equal this value exactly.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions