Microsoft Excel chart making help.

willhub · 5 Apr 2012 at 12:37

Hi.

For my analysis of my questionnaire for my dissertation I have worked out how to calculate how many people may have chosen number 5 (strongly agree) or number 1 (strongly disagree) using a COUNTIF calculation, but some of my other questions I am struggling how I should be working it out to then present it in a chart.

For example:

Basically I've had 145 responses, and for the question "what cycling disciplines do you undertake" and from those people have selected various different combinations.

What I'm wondering is how to do it for the cycling disciplines questions, as when I do for example =COUNTIF(C2:C146,"Track") It won't select track, it will only take the first word.

"Road, Mountain Bike"

The COUNTIF formula will only select road, when I want it to select "Mountainbike"

How do I do that?

Thanks.

HeX · 5 Apr 2012 at 12:48

Change your formula to:

=COUNTIF(C2:C146,"*Track*")

willhub · 5 Apr 2012 at 12:56

Thanks

willhub · 5 Apr 2012 at 13:01

Oh also is it possible for it to add up any other answers, for example I've got some people saying things in that question such as "spinning class at gym", is it possible for it to detect any other answers like that and add them up, so like tying * * with nothing inbetween and it picks up anything other than the choices?

Cheers.

HeX · 5 Apr 2012 at 13:23

CountIF can only do one criteria, so you could do something like:

=COUNTIF(C2:C146, "<>*Track*")

That'd count all the cells that didn't contain the word Track.

However that wouldn't give you what you want as you have multiple standard criteria you want to ignore.

There isn't really an easy way I can think of that would only count text that didn't match a certain list of values

Malakite · 5 Apr 2012 at 16:58

If you know how many people chose your options surely everything else is another option

so sample size of 145

Track = 30
Road = 50
Mountain Bike = 20

anyone who entered their own response is just the difference in totals? Unless you have no responses but you can count them just using a countblank or countif ""