say your sata on sheet1 on is in the range A2 to c6000 where column B
contains the name of the book and column C contains how long it took to
finishe reading.

On sheet 2 where you have the unique names of the books on Column A; type on
cell B2 the following to count # between 0-2 hrs
=SUMPRODUCT(--(Sheet1!$B$2:$B$6000=$A2),--(Sheet1!$C$2:$C$6000<=2))
on cell C2 for COUNT # between 2-3 hrs
=SUMPRODUCT(--(Sheet1!$B$2:$B$6000=$A2),--(Sheet1!$C$2:$C$60002),--(Sheet1!$C$2:$C$6000<=3)))
for 3-4 hrs on D2
=SUMPRODUCT(--(Sheet1!$B$2:$B$6000=$A2),--(Sheet1!$C$2:$C$60003),--(Sheet1!$C$2:$C$6000<=4)))
for 4 hrs on E2
=SUMPRODUCT(--(Sheet1!$B$2:$B$6000=$A2),--(Sheet1!$C$2:$C$60004))

Copy this range B2:E2 all the way down

"swjtx" wrote in
message news:

...

Hi,

What I am trying to do is a kind of distribution. What I want to come
to is something like this:

Book#------How long to read?------
1234 1-2 hours? 2-3 hours? 3-4 hours?
more than 4 hours.

1234 7 11
6 1


The data is on one sheet and I did a quick pivot on sheet2 to get all
unique book numbers. Now in each row on sheet 2 (in the columns next to
the unique book number) I would like the count of how many times a value
(hours) appears in the hours column of the specified book. The first
sheet containing the data looks something like this:

Name Book Hours
Jenny 1234 2.25
Bart 1234 2
Martha 1234 6
Bill 7958 11
Bob 1234 1.5

The trick is to have the function look for a match of the sheet 2 book
number to sheet 1 and count how many times a value appears (on sheet 1)
that falls between a range. Since I have thousands of books, I need the
funtion to search instead of me.

Seems like I need a vlookup combined with a count function or perhaps I
am just confused.

Thanks,

swjtx


--
swjtx
------------------------------------------------------------------------
swjtx's Profile:
http://www.excelforum.com/member.php...o&userid=29716
View this thread: http://www.excelforum.com/showthread...hreadid=494311