Specifying Cells to Count?

[Wojciech]

Hi everyone,

I am having trouble adding in Excel... I have a list of IDs in A:A, and
a list of numbers in B:B. For example:

1 0.1
1 0.5
3 0.1
3 0.2
3 0.3
6 0.1
6 0.5
8 0.3
8 0.9
8 0.8
8 0.4

Now, I need to calculate the number of instances of 0.1 corresponding
to the value of 1 in A:A, the number of instances of 0.1 corresponding
to 3 in A:A, and so on. I've tried FREQUENCY(), COUNTIF(),and a few
others...

I know the number of instances that 1,2 and any other number shows up
in A:A, and I think I can use COUNTIF() -- however, I can't find a way
to dynamically specify the number of cells to count. For example, I
know the range from 1 starts at A2 and goes to A32, and then 2 will go
from A33 to A90. How can I tell Excel to look at the range from A2 to
A(2+31)? I can't hard code it (i.e. A2:A32) because there are so many
integers it would be impractical. I have the number of instances of
each integer in a separate worksheet.

Thank you,
Wojciech

Initiative for Interdisciplinary Research: http://www.i2r.org/
Initiative for Interdisciplinary Research BLOG:
http://i2r.blogspot.com/

 

[Biff]

Hi!

If you have the list of unique numbers on, say, Sheet2 starting in A1:

1
3
6
8

Your raw data is on Sheet1 in the range A1:B100.

On Sheet2 in cell B1 enter this formula:

=SUMPRODUCT(--(Sheet1!A$1:A$100=A1),--(Sheet1!B$1:B$100=0.1))

Copy down as needed.

Biff

 

[Wojciech]

Hi,

Thank you very much! It works great.

- Wojciech