Count cells that contain specific text that are the result of a formula

Clearwat

New Member
Joined
Nov 14, 2013
Messages
8
Good Afternoon all,
I have an index /match formula that produces text results in a single column.
e.g.
string 1
string 2
string 1

Now I am trying to count up the diferrent results using a countif formula and it doesn't seem to be working (Coutif returns 0 if looking for "string 1" and not the expected / hoped for "2"). Is there any way to force the countif formula to read only the formula results and not the index / match formula?

Hope you can help?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Countif wouldn't be much good if it read the actual formula and not the results :)

What formula are you using (the countif one)?
 
Upvote 0
Ok, quick test:

I put this in Sheet1, column F:

PHP:
=IF(K2=1,"string 1","")

and put "1" in several cells in column K.

and then in sheet2 I put this:

PHP:
=COUNTIF(Sheet1!F:F,"string 1")

and the result was 14 (as it should have been).

I'm sure this is an over-simplified version of what you're doing, so maybe it's a problem with the original formula. I should have asked that too...what is the formula that is populating the "string 1" cells?
 
Upvote 0
Just some more info/further testing:

Apparently (I didn't know either) CountIf is not case sensetive. I changed my If formula to "String 1" and "STRING 1" and left the CountIf formula as "string 1" and it always returned 14.

Also, I did make sure there were more than 14 total cells with the IF formula (29 or 30 I think), so if it was counting the actual text within the formula, the result would have been 29 (or 30). So it's definitely not doing that.

Are you sure there's a space between "string" and "1" (or whatever you're really looking for...are you sure it's in the CountIf formula EXACTLY as it is returned in the cells you're counting?
 
Upvote 0
Yes, the test string matches case and no extra spaces. The formula producing the cell contents is index/ match from another tab and seems to work ok, in so far as the correct strings are being produced.
 
Upvote 0
Ok, another thought...where is the (table, I presume) that the index/match formula is looking in come from? If it is imported from a website, there's a chance that the [space] is not just a space, but some other character code. If it was made by you, then I'm at as much a loss as you are. If it's bringing in "string 1" then CountIf should count them.
 
Upvote 0
All the data is local within the spreadsheet. I'll roll up my sleeves and give this another bash! Thanks for your assistance :)
 
Upvote 0
Would it be possible to share your workbook without giving confidential information? This site doesn't allow attachments, but dropbox...google drive...or whatever.

I'm happy to take a look, if it can be done.
 
Upvote 0
All the data is local within the spreadsheet. I'll roll up my sleeves and give this another bash! Thanks for your assistance :)
That wasn't what jproffer meant in Message #7 ... he was referring to the text having been copy/pasted into the worksheet from a website. Websites are notorious for using non-breaking spaces (ASCII code 160) instead of normal spaces (ASCII code 32) in order to treat single words as a grouped word (stops them from being broken apart when located at the end of a displayed line)... they look the same on the screen, but are two completely different characters. If you could share your workbook as jproffer suggests in Message #9 , that would be really helpful. If you can share it, please use DropBox as that is a known safe file-sharing facility.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top