software

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 1The 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: www.excelforum.com/member.php...oamp;userid=29716
View this thread: www.excelforum.com/showthread...hreadid=494311say 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$6000lt;=2))
on cell C2 for COUNT # between 2-3 hrs
=SUMPRODUCT(--(Sheet1!$B$2:$B$6000=$A2),--(Sheet1!$C$2:$C$6000gt;2),--(Sheet1!$C$2:$C$6000lt;=3)))
for 3-4 hrs on D2
=SUMPRODUCT(--(Sheet1!$B$2:$B$6000=$A2),--(Sheet1!$C$2:$C$6000gt;3),--(Sheet1!$C$2:$C$6000lt;=4)))
for gt; 4 hrs on E2
=SUMPRODUCT(--(Sheet1!$B$2:$B$6000=$A2),--(Sheet1!$C$2:$C$6000gt;4))

Copy this range B2:E2 all the way down

quot;swjtxquot; gt; wrote in
message news:

...
gt;
gt; Hi,
gt;
gt; What I am trying to do is a kind of distribution. What I want to come
gt; to is something like this:
gt;
gt; Book#------How long to read?------
gt; 1234 1-2 hours? 2-3 hours? 3-4 hours?
gt; more than 4 hours.
gt;
gt; 1234 7 11
gt; 6 1
gt;
gt;
gt; The data is on one sheet and I did a quick pivot on sheet2 to get all
gt; unique book numbers. Now in each row on sheet 2 (in the columns next to
gt; the unique book number) I would like the count of how many times a value
gt; (hours) appears in the hours column of the specified book. The first
gt; sheet containing the data looks something like this:
gt;
gt; Name Book Hours
gt; Jenny 1234 2.25
gt; Bart 1234 2
gt; Martha 1234 6
gt; Bill 7958 11
gt; Bob 1234 1.5
gt;
gt; The trick is to have the function look for a match of the sheet 2 book
gt; number to sheet 1 and count how many times a value appears (on sheet 1)
gt; that falls between a range. Since I have thousands of books, I need the
gt; funtion to search instead of me.
gt;
gt; Seems like I need a vlookup combined with a count function or perhaps I
gt; am just confused.
gt;
gt; Thanks,
gt;
gt; swjtx
gt;
gt;
gt; --
gt; swjtx
gt; ------------------------------------------------------------------------
gt; swjtx's Profile:
gt; www.excelforum.com/member.php...oamp;userid=29716
gt; View this thread: www.excelforum.com/showthread...hreadid=494311
gt;
I think SUMPRODUCT gets what you want

=SUMPRODUCT(--($B2:$B200=1234),--($C$2:$C$200gt;=2),--($C$2:$C$200lt;3))

which gives the sum of book 1234 betwwen 2-3 hours. Extend that over a
table, and you can then pivot the results.

--

HTH

RP
(remove nothere from the email address if mailing direct)quot;swjtxquot; gt; wrote in
message ...
gt;
gt; Hi,
gt;
gt; What I am trying to do is a kind of distribution. What I want to come
gt; to is something like this:
gt;
gt; Book#------How long to read?------
gt; 1234 1-2 hours? 2-3 hours? 3-4 hours?
gt; more than 4 hours.
gt;
gt; 1234 7 11
gt; 6 1
gt;
gt;
gt; The data is on one sheet and I did a quick pivot on sheet2 to get all
gt; unique book numbers. Now in each row on sheet 2 (in the columns next to
gt; the unique book number) I would like the count of how many times a value
gt; (hours) appears in the hours column of the specified book. The first
gt; sheet containing the data looks something like this:
gt;
gt; Name Book Hours
gt; Jenny 1234 2.25
gt; Bart 1234 2
gt; Martha 1234 6
gt; Bill 7958 11
gt; Bob 1234 1.5
gt;
gt; The trick is to have the function look for a match of the sheet 2 book
gt; number to sheet 1 and count how many times a value appears (on sheet 1)
gt; that falls between a range. Since I have thousands of books, I need the
gt; funtion to search instead of me.
gt;
gt; Seems like I need a vlookup combined with a count function or perhaps I
gt; am just confused.
gt;
gt; Thanks,
gt;
gt; swjtx
gt;
gt;
gt; --
gt; swjtx
gt; ------------------------------------------------------------------------
gt; swjtx's Profile:
www.excelforum.com/member.php...oamp;userid=29716
gt; View this thread: www.excelforum.com/showthread...hreadid=494311
gt;

Hi and Thanks to both of you!

Sumproduct worked. I was looking at that but the arrays were seperated
by an * in the examples I found. I noticed you used quot;--quot;.

One tiny problem remains. As I drag the formula down the page, it
correctly selects the next book# but it also increment the range
searched on the first sheet. Any way to make it choose the same range
without manually editing?

Example:
First Formula:
=SUMPRODUCT(--(Sheet1!$C2:$C200=$A2),--(Sheet1!$C2:$C200gt;=2),--(Sheet1!$C2:$C200lt;3))

Second Formula:

=SUMPRODUCT(--(Sheet1!$C3:$C201=$A3),--(Sheet1!$C3:$C201gt;=2),--(Sheet1!$C3:$C201lt;3))

I want it to choose $A3 but I want it to search the same range
(C2:C200), not increment. Any way to do this?--
swjtx
------------------------------------------------------------------------
swjtx's Profile: www.excelforum.com/member.php...oamp;userid=29716
View this thread: www.excelforum.com/showthread...hreadid=494311Use $c$2:$c$200

The $ signs mean not to adjust the range when you copy the formula.

swjtx wrote:
gt;
gt; Hi and Thanks to both of you!
gt;
gt; Sumproduct worked. I was looking at that but the arrays were seperated
gt; by an * in the examples I found. I noticed you used quot;--quot;.
gt;
gt; One tiny problem remains. As I drag the formula down the page, it
gt; correctly selects the next book# but it also increment the range
gt; searched on the first sheet. Any way to make it choose the same range
gt; without manually editing?
gt;
gt; Example:
gt; First Formula:
gt; =SUMPRODUCT(--(Sheet1!$C2:$C200=$A2),--(Sheet1!$C2:$C200gt;=2),--(Sheet1!$C2:$C200lt;3))
gt;
gt; Second Formula:
gt;
gt; =SUMPRODUCT(--(Sheet1!$C3:$C201=$A3),--(Sheet1!$C3:$C201gt;=2),--(Sheet1!$C3:$C201lt;3))
gt;
gt; I want it to choose $A3 but I want it to search the same range
gt; (C2:C200), not increment. Any way to do this?
gt;
gt; --
gt; swjtx
gt; ------------------------------------------------------------------------
gt; swjtx's Profile: www.excelforum.com/member.php...oamp;userid=29716
gt; View this thread: www.excelforum.com/showthread...hreadid=494311

--

Dave Peterson

Use

=SUMPRODUCT(--(Sheet1!$C$2:$C$200=$A2),--(Sheet1!$C$2:$C$200gt;=2),--(Sheet1!$
C$2:$C$200lt;3))--

HTH

RP
(remove nothere from the email address if mailing direct)quot;swjtxquot; gt; wrote in
message ...
gt;
gt; Hi and Thanks to both of you!
gt;
gt; Sumproduct worked. I was looking at that but the arrays were seperated
gt; by an * in the examples I found. I noticed you used quot;--quot;.
gt;
gt; One tiny problem remains. As I drag the formula down the page, it
gt; correctly selects the next book# but it also increment the range
gt; searched on the first sheet. Any way to make it choose the same range
gt; without manually editing?
gt;
gt; Example:
gt; First Formula:
gt;
=SUMPRODUCT(--(Sheet1!$C2:$C200=$A2),--(Sheet1!$C2:$C200gt;=2),--(Sheet1!$C2:$
C200lt;3))
gt;
gt; Second Formula:
gt;
gt;
=SUMPRODUCT(--(Sheet1!$C3:$C201=$A3),--(Sheet1!$C3:$C201gt;=2),--(Sheet1!$C3:$
C201lt;3))
gt;
gt; I want it to choose $A3 but I want it to search the same range
gt; (C2:C200), not increment. Any way to do this?
gt;
gt;
gt; --
gt; swjtx
gt; ------------------------------------------------------------------------
gt; swjtx's Profile:
www.excelforum.com/member.php...oamp;userid=29716
gt; View this thread: www.excelforum.com/showthread...hreadid=494311
gt;

Hi and thanks a million. It works. --
swjtx
------------------------------------------------------------------------
swjtx's Profile: www.excelforum.com/member.php...oamp;userid=29716
View this thread: www.excelforum.com/showthread...hreadid=494311