Way to tally percentages of data responses *down* columns?

[Jennifre]

Hi, I recently had asked about doing counts across rows, and got a beautiful response that worked perfectly. (see below)

How I'm wondering if I can do it going down columns, and providing the count and the percentage of the total data points in that column.

Is this possible?

Thanks so much!

My row response that worked so well:
=COUNTIF($AL2:$AU2,AV$1)

 

[Jennifre]

Re: Way to tally total and percentages of data responses *down* columns?

To clarify, I want to tally the count and percentage (or just count) of responses that I have down columns.

For example, I have many columns including 1s through 4s, and I'd like to be able to see the total of those numbers beneath each column, like this:

1
4
2
3
1
1
4

What I'd like to see then, is:
3
1
1
2

What I received earlier for this tally across rows worked great. Though my faulty attempt at editing this to work down columns, well, doesn't work.

I tried using this:
=COUNTIF($A2:$A735,COLUMNS($A737:A740))

Though I don't understand how this indicates I receive the total of 1s 2s 3s and 4s, and, well, it is indeed NOT indicating I receive that total.

I am receiving possibly a total of 1s, but not of any other numbers. If I drag it across the columns, I'm getting numbers, but if I drag it down four more rows, I am only getting the same number, four times.

I would be very grateful for any other help!

 

[Haseeb Avarakkan]

Re: Way to tally total and percentages of data responses *down* columns?

If you're copying down, just replace the COLUMNS with ROWS,

=COUNTIF(A$2:A$735,ROWS(A$737:A737))

Copy down...

 

[Jennifre]

Thank you!

However, I must be doing it wrong.

Using your recommendation, or =COUNTIF($A2:$A735,ROWS($A737:A740)), gives me totals that don't make sense, given my 734 data points in this column.

What I get for the totals of 1s, 2s, 3s, and 4s, for this first column, is:
18
17
16
16

What might I be doing wrong?

Thank you!!

 

[Haseeb Avarakkan]

You need to lock the cell references, Enter in first cell, then copy down...

=COUNTIF(A$2:A$735,ROWS(A$737:A737))

See the highlighted dollars sign in Red.

 

[Jennifre]

I see!! THANK YOU for the lesson Haseeb!!

 

[Jennifre]

Hi, thank you again. This is great for counts, though is there a way to also get percentages of the total of responses for each response option?

For example, could I get counts + percentages of the total of responses, for each type of response, like:

1
4
2
3
3

could result in:
1
1
2
1

20% (or .2)
20%
40%
20%

Is that possible?

Thanks so much!!

 

[Jennifre]

And again, I'm just confused.

Once I copy and paste this formula elsewhere, and change the values for its new location, I'm running into one point I just don't understand:

What is the value "ROWS(A$737:A737)" referring to?

I'm getting data that I don't believe is accurate, so I wanted to check that this was specified correctly, but don't know what it refers to, for sure.

I am very, very grateful for any help sent my way!

 

[Haseeb Avarakkan]

is there a way to also get percentages of the total of responses for each response option?

Try,

=COUNTIF(A$2:A$735,ROWS(A$737:A737))/COUNTIF(A$2:A$735,">0")

What is the value "ROWS(A$737:A737)" referring to?

ROWS will Returns the number of rows in a reference or array. So when you enter ROWS(A$737:A737) this will give you 1. How many rows in A$737:A737 = 1,

when you copy down, ROWS(A$737:A738), how many rows in A$737:738 = 2 etc....

 

[Jennifre]

Hi Haseeb, thank you!

Okay, then, given what you have taught me, the formula I am using is not calculating what I need it to calculate.

What I need is a way for it (or something) to return to me these kinds of results:

Given this
1
2
3
4
4
1

I would like to get a total of each number above, or
2 (2 number 1s)
1 (1 number 2)
1 (1 number 3)
2 (2 number 4s)

I received this before for a row, but I [clearly] do not understand how to craft the formula because I am too... dorky.

Thank you again, so much!