Date range criteria and Pivot tables (again!)

[Guest]

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 Govier]

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.

 

[Guest]

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]

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