I have a pivot table with a list of names and number of sales by various dates.

I want to sum the number of sales by person, within a certain date range

date: 11/04/05 13/04/05 20/04/05 24/04/05
Fred 1 2 0 1
John 0 3 1 1
Thomas 1 0 1 1

So if I want to know how many Fred did within 12/04/05 and 21/04/05 date
range (2) then wanted to change the date range it would enable me to do that.

I've tried SUMIF and used AND as the criteria... doesn't seem to work.

Any ideas? A great alternative would be to interpret the pivot table so my
user could see the period by simply changing the date range - is this
possible?

Jim

One way
Assuming your data to be in coumns A:E with your heading in Row 1

Use G1 to hold your start date 12/04/05
USe H1 to hold your end date 21/04/05

In cell G2 enter
=SUMPRODUCT(--($B$1:$E$1=$G$1),--($B$1:$E$1<=$H$1),B2:E2)
and copy down through as many rows as you have data.

Varying the dates in G1 and H1 will give you the relevant count for each
person.

--
Regards
Roger Govier
"DerbyJim1978" wrote in message
...
I have a pivot table with a list of names and number of sales by various
dates.

I want to sum the number of sales by person, within a certain date range

date: 11/04/05 13/04/05 20/04/05 24/04/05
Fred 1 2 0 1
John 0 3 1 1
Thomas 1 0 1 1

So if I want to know how many Fred did within 12/04/05 and 21/04/05 date
range (2) then wanted to change the date range it would enable me to do
that.

I've tried SUMIF and used AND as the criteria... doesn't seem to work.

Any ideas? A great alternative would be to interpret the pivot table so
my
user could see the period by simply changing the date range - is this
possible?

Jim

Roger - didn't think of using sumproduct, thank you!

Can I just ask you what the two sets of -- are for? Never seen them used
like that...

Regards

Jim

"Roger Govier" wrote:

One way
Assuming your data to be in coumns A:E with your heading in Row 1

Use G1 to hold your start date 12/04/05
USe H1 to hold your end date 21/04/05

In cell G2 enter
=SUMPRODUCT(--($B$1:$E$1=$G$1),--($B$1:$E$1<=$H$1),B2:E2)
and copy down through as many rows as you have data.

Varying the dates in G1 and H1 will give you the relevant count for each
person.

--
Regards
Roger Govier
"DerbyJim1978" wrote in message
...
I have a pivot table with a list of names and number of sales by various
dates.

I want to sum the number of sales by person, within a certain date range

date: 11/04/05 13/04/05 20/04/05 24/04/05
Fred 1 2 0 1
John 0 3 1 1
Thomas 1 0 1 1

So if I want to know how many Fred did within 12/04/05 and 21/04/05 date
range (2) then wanted to change the date range it would enable me to do
that.

I've tried SUMIF and used AND as the criteria... doesn't seem to work.

Any ideas? A great alternative would be to interpret the pivot table so
my
user could see the period by simply changing the date range - is this
possible?

Jim

Hi Jim

The double unary -- is used to coerce the True or False outcome of the test
to a 1 or 0 in order that sumproduct can make the calculations.
I always used to use * rather than , in my formulae until it was pointed out
to me recently by other members of the NG that the use of the double unary
is marginally more efficient.

I was pointed to the following reference which you may also find useful.
http://xldynamic.com/source/xld.SUMPRODUCT.html


--
Regards
Roger Govier
"DerbyJim1978" wrote in message
...
Roger - didn't think of using sumproduct, thank you!

Can I just ask you what the two sets of -- are for? Never seen them used
like that...

Regards

Jim

"Roger Govier" wrote:

One way
Assuming your data to be in coumns A:E with your heading in Row 1

Use G1 to hold your start date 12/04/05
USe H1 to hold your end date 21/04/05

In cell G2 enter
=SUMPRODUCT(--($B$1:$E$1=$G$1),--($B$1:$E$1<=$H$1),B2:E2)
and copy down through as many rows as you have data.

Varying the dates in G1 and H1 will give you the relevant count for each
person.

--
Regards
Roger Govier
"DerbyJim1978" wrote in message
...
I have a pivot table with a list of names and number of sales by various
dates.

I want to sum the number of sales by person, within a certain date
range

date: 11/04/05 13/04/05 20/04/05 24/04/05
Fred 1 2 0 1
John 0 3 1 1
Thomas 1 0 1 1

So if I want to know how many Fred did within 12/04/05 and 21/04/05
date
range (2) then wanted to change the date range it would enable me to do
that.

I've tried SUMIF and used AND as the criteria... doesn't seem to work.

Any ideas? A great alternative would be to interpret the pivot table
so
my
user could see the period by simply changing the date range - is this
possible?

Jim