Counting by answer per column

[whhkwhhk]

Hi everyone

Here is my problem. I have an excel table with different columns filled with the same answers : yes ; no ; n/a ; -
Here is a visual idea of what this table looks like :
[TABLE="width: 200"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]yes
[/TD]
[TD]yes
[/TD]
[TD]no
[/TD]
[/TR]
[TR]
[TD]no
[/TD]
[TD]n/a
[/TD]
[TD]no
[/TD]
[/TR]
[TR]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]yes
[/TD]
[/TR]
[TR]
[TD]n/a
[/TD]
[TD]yes
[/TD]
[TD]no
[/TD]
[/TR]
</tbody>[/TABLE]

And so on.

Now, I would like to count the number of yes ; no ; n/a ; - per column. (here for instance, for column A, it is : Yes : 1 ; No : 1 ; - : 1 ; N/A : 1)
First, I thought to use a pivot table. My problem is, I know how to do it column by column, but that way it will take ages.
So, I would like to know if there is a way to use a pivot table to do all the table at once, or if there is a better way than using a pivot table to do what I want to obtain.

Thanks,

 

[makexcel]

you have tried using the function '=COUNTIF (A:A; "=yes") ' and then drag ?.
=COUNTIF (A:A; "=yes")
=COUNTIF (A:A; "=no")
=COUNTIF (A:A; "=n/a")
=COUNTIF (A:A; "=-")

For such a simple table it is a bit wasted the pivot table.

 

[whhkwhhk]

Dear makexcel,

Thanks for the quick answer.

Actually, I didn't want to use Countif, I can't remember why...
Anyway, that definitively seems to be the best way to obtain the results I'm looking for.

And let say that I want to complicate a bit and count the answers yes, only when the answers in the other columns are different from yes. In that way it would give me column A, yes : 0 since for this column, the only time there is a yes answer (raw 1) there is also a yes in column B. I hope it's clear.

Do you know a way to do it ?

Thanks,

 

[makexcel]

you should set up a support column " D ", which counts the " yes " value per line and then use a conditional sum , or I can recommend a VBA function . Unfortunately now I can not help you with PIVOT tables ...

Public Function xCountYes(xRange As Range, xCol)
 'xRange is range to be analyzed
 'xCol is column that contains the value yes
 Dim x1 As Long
 For x1 = 1 To xRange.Rows.Count
  If Application.CountIf(xRange.Rows(x1), "yes") > 1 And xRange(x1, xCol) = "yes" Then
   xCountYes = xCountYes + 1
  End If
 Next x1
End Function

 

[whhkwhhk]

Dear makexcel,

Thank you for your help, it will be very helpful.

Best,