MacExcel Formulas - countif function is limited

[JOshforde]

Hi there,

I'm seeking a little advice about MacExcel system functionality. I have failed with a =countif function and a colleague advised he used a pc with the same formula and it worked.
First, as a new Mac user, I'm a little confused between teething frustrations and actual limitations of the systems so feel free to call me out on any silly stuff
I'll explain as clearly as I can the problem, I'm not 100% that a pc will resolve this as I don't have one any more to check for myself. But I think it might be rooted in VBA limitations or similar.


PROBLEM

=countif(range,criteria) - criteria is *like* succeeds where I manually type that text criteria contents into the cell yet fails when I specify 'like' text referring to a cell.

EXAMPLE :
So I have results of a business survey. If a user selects 5 answers to a question, those 5 answers are reported in one cell. I want to count the number of each answer in the answer set to analyse the results.


cell A1
"We understand current staff productivity, capability, capacity and spare time We understand types of ...blah bla" (two actual answers in the dataset starting with We).

Function :
=COUNTIF(A1:BQ31,"*We understand current staff productivity, capability, capacity and spare time*") returns a positive value (in my dataset = 4)

whereas =COUNTIF(A1:BQ32,"*BP35*") where BP35 = the above answer data 'we understand...' fails to return values. (=0)



a colleague tested the above formula on a pc at home and it apparently worked.


The point of my doing these countif functions is to get an array of the 10 answer responses per question. being able to refer =countif to answer data means I can quickly get these figures, typing directly into cells isn't as scalable (I have about 700 possible answers in the total dataset.

HELP :

Could you please tell me if there is a reason my Countif criteria is failing, is it just too complex or is there some compatibility issue I'm up against?

Your advice very welcome and much appreciated in advance

 

[exegete77]

Howdy. Couple things, so not sure they will help. This is not related to VBA or lack of in Macs.

1. You are using different ranges in the two equations.

2. How is the cell formatted for your second example.

3. Sometimes, it works better to use SUMPRODUCT as a multiple COUNTIF. Check out Charley's site for further explanation.

 

[JOshforde]

Thanks

Just acknowledging the response. Thank you.
I've had a cursory look at the SUMPRODUCT function but will need to give it more attention tonight at home.

So reading the above I can see my issue isn't so much the countif function but figuring out how to get Excel to parse cell data for a string of text that matches the string of text in a specified cell rather than inputting the string manually into the formula.

 

[JOshforde]

Cracked It



Well, I may not be the smartest man alive but at least I'm a stupid man with my answer

The problem is that I was inserting the wildcards at a formula level which Excel was rejecting unless I bracketed the criteria in ("") thus meaning it wasn't applying the criteria, just searching the text that would be "*criteria example*" exactly.

If I insert the wildcards into the cell itself, things change and it works. This is relatively simple to add in using the CONCATENATE function to change the cells.

Basically I know on a SQL level what I'm trying to do but I've only ever used MS Access which prob doesn't offer this level of flexibility to build. So Excel works ok for me.,

Let me show you for the next person who's stuck, and to show off a little.

Example text data :
Does your business have issues with?
Meeting loan or debt obligations- often
Meeting loan or debt obligations- often
Meeting loan or debt obligations- rarely

Define range = "Rangetest"

Criteria
B241 Meeting loan or debt obligations- often
B242 *often*
B243 *Meeting loan or debt*
B244 often

Bad Formulae
=COUNTIF(rangetest,"*B244*") result = 0 this is seeking the specific text string, not parsing the cell using the wildcards as desired.

Good formulae
=COUNTIF(rangetest,B241) = 2
=COUNTIF(rangetest,B242) = 2
=COUNTIF(rangetest,B243) = 3
=COUNTIF(rangetest,B244) = 0



Glorious success. It all seems so simple now that I've banged my head against the monitor for a few hours, but I guess it saves me time in the long run.

 

[nadyne]

In the future, if you find yourself banging your head on Excel, there are lots of Excel gurus who hang out at the Excel:Mac product forums.

Regards,
Nadyne.