The data is contained in Q5:Q300.

This is the pattern of the data and the pattern where the numbers are
concerned are random numbers between 0 and 6. ex. Q5=2,
Q6=4,Q7=1,Q8=""(blank cell),Q9=1,Q10=0,Q11=2,Q12=""(blank cell). . .

This pattern continues throughout the colum, number,number,number,blank
cell, number,number,number,blank cell and continues to repeat and updated
causing the rows in column to increase.

The problem is I have to be able to count (not sum) how many 0's, 1's, 2's,
3's, 4's, 5's, and 6's there are but have to count them in the first cell of
all paterns, the second cell of the patterns and the third cell of the
patterns.

So, in basic explanation, I need to know how many 1's there are in Q5, Q9,
Q13 and so on and then count how many 4's there are in Q6, Q10, Q14 but
never all cells in the column like a sum would be. I tried fooling it by
using SUM and then dividing by the number I needed to find out the amount of
until I realized SUM only allowed 30.

Thanks

Countif does not allow you to do anything other than ranges? Is there
another way?

1) put a title in cell Q4 "Numbers"
2) add a new column to the left of your data (column P) and call it "RowGroup"
3) in this column enter group1, group2, group3, blank group, group1, group2,
group3 etc etc, all the way down to the bottom. From P9 onwards you can use
=P5 and extend downwards.
4) create a pivot table on your 2 columns. Select columns, then datapivot
table finish.
5) in this sheet, drag and drop
a) "RowGroup" into the columns position (above the data)
b) "Numbers" into the rows (to the left of the data)
c) Now drag numbers again from the field list into the data position.

You should now have a count of where each of your numbers fall.
--
Allllen


"Shu of AZ" wrote:

The data is contained in Q5:Q300.

This is the pattern of the data and the pattern where the numbers are
concerned are random numbers between 0 and 6. ex. Q5=2,
Q6=4,Q7=1,Q8=""(blank cell),Q9=1,Q10=0,Q11=2,Q12=""(blank cell). . .

This pattern continues throughout the colum, number,number,number,blank
cell, number,number,number,blank cell and continues to repeat and updated
causing the rows in column to increase.

The problem is I have to be able to count (not sum) how many 0's, 1's, 2's,
3's, 4's, 5's, and 6's there are but have to count them in the first cell of
all paterns, the second cell of the patterns and the third cell of the
patterns.

So, in basic explanation, I need to know how many 1's there are in Q5, Q9,
Q13 and so on and then count how many 4's there are in Q6, Q10, Q14 but
never all cells in the column like a sum would be. I tried fooling it by
using SUM and then dividing by the number I needed to find out the amount of
until I realized SUM only allowed 30.

Thanks

Countif does not allow you to do anything other than ranges? Is there
another way?

Hi,

You can have a cross tabulation: On the vertical, say cells S2:S8 you
can put the values you want to count 0-6. On the horizontal (T1:V1) you
can put the values 5, 6, 7 as mnemonics of the rows of the first bunch.

Then in T2 you can put the following formula

=SUMPRODUCT(($Q$6:$Q$300=$S2)*($Q$6:$Q$300<"")*(M OD(ROW($Q$6:$Q$300),4)=MOD(T$1,4)))

HTH
Kostis Vezerides


Shu of AZ wrote:
The data is contained in Q5:Q300.

This is the pattern of the data and the pattern where the numbers are
concerned are random numbers between 0 and 6. ex. Q5=2,
Q6=4,Q7=1,Q8=""(blank cell),Q9=1,Q10=0,Q11=2,Q12=""(blank cell). . .

This pattern continues throughout the colum, number,number,number,blank
cell, number,number,number,blank cell and continues to repeat and updated
causing the rows in column to increase.

The problem is I have to be able to count (not sum) how many 0's, 1's, 2's,
3's, 4's, 5's, and 6's there are but have to count them in the first cell of
all paterns, the second cell of the patterns and the third cell of the
patterns.

So, in basic explanation, I need to know how many 1's there are in Q5, Q9,
Q13 and so on and then count how many 4's there are in Q6, Q10, Q14 but
never all cells in the column like a sum would be. I tried fooling it by
using SUM and then dividing by the number I needed to find out the amount of
until I realized SUM only allowed 30.

Thanks

Countif does not allow you to do anything other than ranges? Is there
another way?