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)

 

[Haseeb Avarakkan]

When you copy down the formula, you need to lock the range in COUNTIF & First cell in the ROWS with $ sign;

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Field 1</td><td style="font-weight: bold;;">Count</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$7,ROWS(<font color="Red">B$2:B2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$7,ROWS(<font color="Red">B$2:B3</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$7,ROWS(<font color="Red">B$2:B4</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$7,ROWS(<font color="Red">B$2:B5</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Jennifre]

Hi, as usual, thank you so much, Haseeb!

While I can see and understand how to "lock" the range into this formula, I do not see or understand how the formula shows WHICH NUMBERS it is counting.

Again, ideally, I'd have something like this:
1
3
2
2
4
1
3

And would get this returned by this formula, as it summed up how many 1s, 2s, 3s, and 4s there are in the cells above it, or:
2 [1s]
2 [2s]
2 [3s]
1 [4]

I've tried looking this up online also, and do not see a way that makes writing this formula understandable to me. Sorry!! But thanks SO much!!

 

[Haseeb Avarakkan]

Let's say;

A2:A8 contains the data. So in C2 enter; & copy down...

=ROWS(C$2:C2)

Here you says, how many row numbers are in range C2:C2? Ans: 1

When you copy down you will get;

=ROWS(C$2:C3) Ans: 2 (Row #2, Row #3)
=ROWS(C$2:C4) Ans: 3 (Row #2, Row #3, Row #4)
=ROWS(C$2:C5) Ans: 4 (Row #2, Row #3, Row #4, Row #5)

Then in D2 enter the below formula, copy down...

=COUNTIF($A$2:$A$8,C2)

In C2 you should have 1 ( =ROWS(C$2:C2) )

COUNTIF will calculate C2 value as the Criteria. When you copy down, you will get

=COUNTIF($A$2:$A$8,C3) calculate C3 value as the Criteria.
=COUNTIF($A$2:$A$8,C4) calculate C3 value as the Criteria.
=COUNTIF($A$2:$A$8,C5) calculate C4 value as the Criteria.

E2 enter this formula;

=COUNTIF($A$2:$A$8,ROWS(E$2:E2))

So here, firstlt calculate ROWS(E$2:E2) part & will give you 1 [ How many row numbers are in E2:E2? Ans: 1]

COUNTIF will calculate as;

=COUNTIF($A$2:$A$8,1)

When you copy down, ROWS calculate it's values, 2,3,4.

See below.

Hope this helps you.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Data</td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">ROWS</td><td style="font-weight: bold;text-align: center;;">COUNTIF</td><td style="font-weight: bold;text-align: center;;">COUNTIF / ROWS</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=ROWS(<font color="Blue">C$2:C2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$8,C2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$8,ROWS(<font color="Red">E$2:E2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=ROWS(<font color="Blue">C$2:C3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$8,C3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$8,ROWS(<font color="Red">E$2:E3</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=ROWS(<font color="Blue">C$2:C4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D4</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$8,C4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$8,ROWS(<font color="Red">E$2:E4</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=ROWS(<font color="Blue">C$2:C5</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$8,C5</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E5</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$8,ROWS(<font color="Red">E$2:E5</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Jennifre]

Hi again,
Gosh this WAS so helpful when I asked before; thank you!!

Returning now to look this up for a similar project, I will need to use a series of data in the cell I'm asking this formula to count. I am trying to use a version of this formula to COUNTIF:

=COUNTIF(DH$2:DH$127,DG129)

My reference data looks like this, which is in column DG. In other words as the above formula goes down column DH I need it to count ANY of the numbers listed in DG, as seen below. The column I'm hoping this formula to count is in DH2:DH127. This formula is not accurately depicting what's in column DG due to the commas -- how can I specify these series of numbers to find in my column DH of data?

Column DG looks like this:
1111
1112, 1121, 1122
1311, 1333
1231
1332, 1333, 1223

The data in column DH is like this:
1111
1231
1333
1112
1121
1122
1311
1333
etc

I need the formula to count ALL the numbers it sees in column DH that match each number in the column of DG.

Excel is converting (1111,1121, etc) to a regular number (11,111,121) while I need it to COUNT each of those! Eek.

Any help anyone can give will be greatly appreciated -- thanks so much in advance!