counting nightmare..
Posted by Simon Canada on July 19, 2000 12:00 AM
I am trying to count on the following basis
A B C D E
1 2 5
2 2
3 3 3
4 3 2
5 3
6 5 7
7 5
8 2 1
I wish to count the number of times a number occurs in column A , with a number occuring next to it in column B, giving a count by each number in A , such that I know the number of times that 3 occurs in column A without a blank cell next to it in B, and then by each number occuring in A. The result would be 2 for the number of times that 3 occurs in A with a number in B.
Posted by david on July 19, 0100 12:27 AM
HUH?
wanna try to make that a little clearer I read it five times and my head is still spinning.
Posted by Ada on July 20, 0100 1:02 AM
A B C D E _________________________________________
Simon
I think I understand what info you need but the format of the table you want does not come out clearly on the message board. Suggest you provide details of what you need as follows :-
ColA - Positive whole numbers of any value and any sequence. Number of rows can vary. No blank cells.
ColB - Either numbers or blanks. Rows with data will not extend further than Col A rows.
ColC - (Description required)
ColD - (Description required)
ColD - (Description required)
Ada
Posted by Ada on July 19, 0100 2:23 AM
Simon
Create a Name called Data for the range of data in Column A.
Enter the following formula in cell C1 and drag it down the column.
=IF(B1="","",COUNTIF(data,data))
This does not produce a summary - it shows in column C the count of the number in col A of the same row.
(Darren - sorry if this is not too clear)
Ada
Posted by Ada on July 19, 0100 2:25 AM
Sorry - I mean't David not Darren
Posted by Ada on July 19, 0100 6:02 AM
Let's try again
I'm having a bad day. The previous formula doesn't work. Start again :-
Put the following formula in C1 and drag it down :-
=IF(B1="","",A1)
Create a Name called Data and put the following in the RefersTo box :-
=$C$1:$C$1000
If the rows are likely to be more than 1000, increase the formula accordingly.
Put the following formula in D1 and drag it down :-
=IF(C1="","",COUNTIF(data,data))
I don't particularly like the above because it uses two columns. There must be a way of arriving at one formula in one column - I will post again if I manage to work it out.
But let me know if you don't need it.
Ada
Posted by Simon C on July 19, 0100 5:00 PM
1 | 2 5
Yeah, I know, it's difficult to verbalise these problems. So I'll summarise
-in Column A, numbers from 1 to infinity occur,
-the numbers in column A are not numerically ascending or descending, they occur in any order, and the numbers often repeat, ie. there is no sequence
-in Column B, either a number, or, a blank cell will occur
- the value of the number in Column B is unimportant, it only matters whether the cell contains a number, or is blank
I want to know
-the number of occasions when a specific number occurs in column A, without a blank cell in column B.
- I wish to build a table thus,
A B C D E _________________________________________
1|Actual No.|Occurences of No. in A
Column A | without a blank cell in Col. B
2| -------------------------------------
3| 1 | 52
4| 2 | 67
5| 3 | 73
6| 4 | 85
7| 5 | 92
Etc.
I hope this clarifies my dilema..
