Countif help

[Ross_Garland]

Hi!
I’m quite an excel noob and need some help with two things!

I’m doing a survey as part of my MSc thesis and I’ve created a spreadsheet that pulls together all the important information.
I know these are very simple problems (I just can’t seem to get anywhere – takes me back to my days of using R!)

Problem 1;
I’m trying to compare public opinion of water quality to the actual water quality, so I want to write a condition that counts when the text within two cells is the same.

Problem 2;
I’m trying to look at water user perceptions, which is comprised of three categories and break up these results further. I’ve tried using the COUNTIF function with no use.

=COUNTIFS(Responses!$G$2:$G$300,"Kayak", Responses!$G$2:$G$300,"Swim", ", Responses!$G$2:$G$300,"Watersports”, Responses!$C$2:$C$300,"Male")

That was my useless attempt. I can see why it doesn’t work (probably need to use an OR in there or something?), but thought it might help to show what I’m trying to do?

- Count all the rows that are either Swim, Kayak or Watersports that are Male.

Apologies if this is cryptic and difficult to understand – Seeing double after a 12 hour shift in the lab and now trying to get this sorted .

Any help would be greatly appreciated!

Cheers,
Ross

[Ron Coderre]

I *think* this is what you're trying to achieve...

This formula returns the count of Col_G items containing Swim, Kayak, or Watersports where the corresponding Col_C value is Male

Please Login or Register  to view this content.

The inner section (COUNTIFS) returns an array of 3 values pertaining to the Swim, Kayak, or Watersports categories. The SUM function totals those values.

Is that something you can work with?

[Jonmo1]

Countifs (and sumifs and averageifs) treats the criteria as AND, not OR.
So it will only count a row if ALL 4 criteria are met.
And no cell in column G can be Kayak AND Swim AND Watersports.

To do that as an or, try like this

=SUM(COUNTIFS(Responses!$G$2:$G$300,{"Kayak","Swim","Watersports"}, Responses!$C$2:$C$300,"Male"))

[shg]

Welcome to the board.

=SUM(COUNTIFS(Responses!$C$2:$C$300, "Male", Responses!$G$2:$G$300, {"Kayak","Swim","Watersports"}))