Count values from Pivot Table

[lesg46]

Sorry - I'm asking for help again!

Using Excel 2002 I have a sheet with many thousands of rows of data and I'm
trying to use pivot tables to analyse my data as follows:

Here’s a sample of my data

Date Product Qty Customer
01/09/2007 Apple 1 2317
01/09/2007 Banana 1 2317
01/09/2007 Carrot 1 2317
01/09/2007 Apple 1 2324
01/09/2007 Apple 1 2326
01/10/2007 Apple 1 2352
01/10/2007 Banana 1 2352
01/10/2007 Apple 1 2354
01/10/2007 Apple 2 2365
01/10/2007 Carrot 1 2367
01/10/2007 Apple 1 2370
01/10/2007 Apple 1 2382
01/10/2007 Banana 1 2382
01/10/2007 Apple 1 2382
01/10/2007 Apple 1 2383
01/10/2007 Carrot 1 2383
01/10/2007 Apple 1 2385
01/10/2007 Apple 1 2386
01/10/2007 Carrot 2 2386

Here’s my pivot table
Sum of Qty
Date Customer Total
01/09/07 2317 3
2324 1
2326 1
01/09/07 Total 5
01/10/07 2352 2
2354 1
2365 2
2367 1
2370 1
2382 3
2383 2
2385 1
2386 3
01/10/07 Total 16
Grand Total 21

What I’m trying to achieve is a daily count of number of items purchased. I
don't care about who bought what item.

i.e.
1 item 2 items 3 items
1/9/2007 2 1
1/10/2007 4 3 2

Can this be done using pivot tables, or am I barking up the wrong tree? I
had thought I might need to create another pivot table based on my original
one, however I can’t analyse by the results in the ‘total’ column.

As always, all help very gratefully received. My Excel knowledge is vastly
improved thanks to this forum, but I’ve obviously still got a way to go!

Sorry it's such a lengthy question.

Many thanks

Les

 

[Max]

One way .. add a helper col E in the source table
Label in E1: #Items
In E2:
=IF(A2="","",IF(SUMPRODUCT((A$2:A2=A2)*(D$22=D2))<COUNTIF(D,D2),"",COUNTIF(D,D2)))
Copy E2 down. Pivot on the extended source table, place Date in ROW, #Items
in COLUMN, #Items in DATA (as Count) to return the required results
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik

 

[Ashish Mathur]

Hi,

You may also use Calculated formulas and calculated items in pivot tables.
Please free to mail your question to me at (e-mail address removed)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Max]

Sorry, correction to the earlier response ..

One way .. add 2 helper cols E & F in the source table
Labels in E1:F1, eg: Int, #Items
In E2:
=IF(A2="","",SUMPRODUCT((A$2:A2=A2)*(D$22=D2)))

In F2, array-entered (press CTRL+SHIFT+ENTER to confirm the formula):
=IF(E2<MAX(IF((A$2:A$100=A2)*(D$2$100=D2),E$2:E$100)),"",E2)
(adapt the ranges to suit your actual extents)

Copy E2:F2 down. Pivot on the extended source table, place Date in ROW,
#Items
in COLUMN, #Items in DATA (as Count) to return the required results
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik
---

 

[Max]

You may also use Calculated formulas and calculated items in pivot tables.
I'm not sure if that's possible in this instance. Given the OP's sample data
as posted, how would the steps to do it using calculated formulas/items look
like? (I don't know)

Please free to mail your question to me at ...

But wouldn't it be much better to benefit all (ie the OP & all other
interested readers) if queries & responses/answers are kept online/visible
here in the newsgroups? That's the way I understand it & practise.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800, Files:359, Subscribers:56
xdemechanik
---

 

[Ashish Mathur]

Hi,

How are you Max. I am absolutely in sync with what you say. The only
reason I mention that statement is many a times it is difficult to
understand the problem without a spreadsheet. I will nevertheless try to
answer the question in the newsgroups itself.

Thank you for the feedback.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[lesg46]

Hi Max,
This almost works!
Here’s the result I get with your formulas.

date Product qty Cust. Int Items
12/01/2007 Apple 1 6057 1 1
12/01/2007 Apple 2 6058 1
12/01/2007 Banana 1 6058 2 2
12/01/2007 Apple 1 6059 1
12/01/2007 Banana 1 6059 2
12/01/2007 Carrot 2 6059 3 3


However it doesn't take into account the quantity of each item that the
customer may have bought, just the number of times the customer number
appears on a particular day. Somehow Column C (Qty) needs to come into play
too. So that for customer 6058 I get the result of 3, and for 6059 I get 4.

I’m now way out of my depth, so if you can help further I’d be most grateful.

Thanks again,
Lesley

 

[Max]

In E2: =IF(A2="","",SUMPRODUCT((A$2:A2=A2)*(D$22=D2)))

Try changing the formula in E2 to:
=IF(A2="","",SUMPRODUCT((A$2:A2=A2)*(D$22=D2),C$2:C2))
Copy down. Refresh the pivot.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik

 

[lesg46]

Hi Max,
Have tried your revised formula but I'm afraid it doesn't work.
I get this result
date Product qty cust. Int Items
12/01/2007 Apple 1 6057 1
12/01/2007 Apple 2 6058 1
12/01/2007 Banana 1 6058 1
12/01/2007 Apple 1 6059 1
12/01/2007 Banana 1 6059 1
12/01/2007 Carrot 2 6059 1

All Column E changes to 1 and no item counts.
Lesley

 

[Max]

Pl re-try/check again over there

I got the results below using the revised E2, copied down
(no change to col F's formulas)

date Product qty cust Int Items
12/1/2007 Apple 1 6057 1 1
12/1/2007 Apple 2 6058 2
12/1/2007 Banana 1 6058 3 3
12/1/2007 Apple 1 6059 1
12/1/2007 Banana 1 6059 2
12/1/2007 Carrot 2 6059 4 4

The cols E & F construct for the above, once again
E2, copied down:
=IF(A2="","",SUMPRODUCT((A$2:A2=A2)*(D$22=D2),C$2:C2))

F2, array-entered*, then copied down:
=IF(E2<MAX(IF((A$2:A$100=A2)*(D$2$100=D2),E$2:E$100)),"",E2)
*press CTRL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik

 

[lesg46]

Hi Max,

After much confusion I have got it to work. I had checked and double
checked the formulas yesterday in the office, but it was doing some very
strange things... Returning different values each time or sometimes returning
no values at all (all using the same formula). I recreated the spreadsheet
on my home PC last night, which is running Excel 2003 and it worked first
time! Back in the office this morning with 2002, similar problems to
yesterday, so I started all over again from the beginning with a clean sheet
with all my data in, and I finally have the results I need. Seems on my PC
if I don't get it right first time, it doesn't work at all.
Perhaps I need to upgrade both my office PC and Excel!

Many many thanks for all your help and patience with this. You've saved me
a huge amount of time and severe boredom from doing it almost manually!

Best regards
Lesley

 

[Max]

Welcome, glad you got it working !
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik