Double lookup and Frequency Counter

jmc226

New Member
Joined
Nov 25, 2005
Messages
1
Hello,

I am trying to count the frequency of X and Y values that are in a certain range. For example the data is:

X Y
0.1 0.2
0.2 0.2
0.0 0.2
0.1 0.2
0.0 0.2
0.1 0.2

And I want to count the frequency of each for example:

X Y Frequency
0 0 0
0 0.1 0
0 0.2 2
0.1 0 0
0.1 0.1 0
0.1 0.2 3
0.2 0 0
0.2 0.1 0
0.2 0.2 1

So I need a way of comparing the 2 values in the data with the two values in the binning columns and be able to count the frequency.

If you know a way to do this please let me know! thanks :biggrin:

J.C.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,403
Assuming that A1:B6 contains your data, and D1:E9 contains your 'binning' values, try...

F1, copied down:

=SUMPRODUCT(--($A$1:$A$6=D1),--($B$1:$B$6=E1))

Hope this helps!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,797
Messages
5,574,366
Members
412,589
Latest member
ArtBOM
Top