Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 69
Default Insert formula in a pivot table

Hi!
How can i insert a formula to count the suppliers per date?
The pivot table appears like this:

Date Supplier Total
07-07-2007 20002 4
20007 1
20020 10
07-07-2007 Total 15
08-07-2007 21025 2
20545 7
07-07-2007 Total 9

I need to put the count of suppliers per date.
So, the count of suppliers would be 3 on 07-07-2007 and 2 on 08-07-2007.
Thanks a lot!
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 358
Default Insert formula in a pivot table

Does each suppier only have one entry on your list for each date? ie - is
the entry for 20002 on the 7th = 4; or is it made up of four single entries?
If it's the former, then adding a Count of Supplier into your data field of
the Pivot table should return the desired result. If it's single entries
then I'd need to have another think about it!!

"Nelson" wrote:

Hi!
How can i insert a formula to count the suppliers per date?
The pivot table appears like this:

Date Supplier Total
07-07-2007 20002 4
20007 1
20020 10
07-07-2007 Total 15
08-07-2007 21025 2
20545 7
07-07-2007 Total 9

I need to put the count of suppliers per date.
So, the count of suppliers would be 3 on 07-07-2007 and 2 on 08-07-2007.
Thanks a lot!

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 69
Default Insert formula in a pivot table

In the data base they have more then one entry for each date but not in the
pivot table and the question here is how to put it in the pivot table, being
the data base his own source (if I use formulas in the data base, the files
gets extremely slow!).
The entry for 20002 on the 7th is the count of 4 entries in the data base.
So, what I need is to get the count of suppliers in the pivot table and
being the data base the source of that count.
I hope it's clear :-)


"Andrew" wrote:

Does each suppier only have one entry on your list for each date? ie - is
the entry for 20002 on the 7th = 4; or is it made up of four single entries?
If it's the former, then adding a Count of Supplier into your data field of
the Pivot table should return the desired result. If it's single entries
then I'd need to have another think about it!!

"Nelson" wrote:

Hi!
How can i insert a formula to count the suppliers per date?
The pivot table appears like this:

Date Supplier Total
07-07-2007 20002 4
20007 1
20020 10
07-07-2007 Total 15
08-07-2007 21025 2
20545 7
07-07-2007 Total 9

I need to put the count of suppliers per date.
So, the count of suppliers would be 3 on 07-07-2007 and 2 on 08-07-2007.
Thanks a lot!

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 358
Default Insert formula in a pivot table

The only thing I can think of would be to add a couple of summary formulae at
the side of the pivot table; eg:

=IF(RIGHT(A5,5)="total",A5,"") in column D,
=IF(RIGHT(A5,5)="total",COUNTA($B$5:B4)-SUM($e$5:e4),"") in column E

where column A is your date column in the pivot table, column B is the
supplier and C is the total, and the first row of data is in row 5. This
should give you summary in these columns.

Not the tidiest, but a potential solution.

"Nelson" wrote:

In the data base they have more then one entry for each date but not in the
pivot table and the question here is how to put it in the pivot table, being
the data base his own source (if I use formulas in the data base, the files
gets extremely slow!).
The entry for 20002 on the 7th is the count of 4 entries in the data base.
So, what I need is to get the count of suppliers in the pivot table and
being the data base the source of that count.
I hope it's clear :-)


"Andrew" wrote:

Does each suppier only have one entry on your list for each date? ie - is
the entry for 20002 on the 7th = 4; or is it made up of four single entries?
If it's the former, then adding a Count of Supplier into your data field of
the Pivot table should return the desired result. If it's single entries
then I'd need to have another think about it!!

"Nelson" wrote:

Hi!
How can i insert a formula to count the suppliers per date?
The pivot table appears like this:

Date Supplier Total
07-07-2007 20002 4
20007 1
20020 10
07-07-2007 Total 15
08-07-2007 21025 2
20545 7
07-07-2007 Total 9

I need to put the count of suppliers per date.
So, the count of suppliers would be 3 on 07-07-2007 and 2 on 08-07-2007.
Thanks a lot!

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 69
Default Insert formula in a pivot table

I thought about that also but, since it's not so dynamic, I was trying to see
all possibilities.
Thanks a lot anyway!

"Andrew" wrote:

The only thing I can think of would be to add a couple of summary formulae at
the side of the pivot table; eg:

=IF(RIGHT(A5,5)="total",A5,"") in column D,
=IF(RIGHT(A5,5)="total",COUNTA($B$5:B4)-SUM($e$5:e4),"") in column E

where column A is your date column in the pivot table, column B is the
supplier and C is the total, and the first row of data is in row 5. This
should give you summary in these columns.

Not the tidiest, but a potential solution.

"Nelson" wrote:

In the data base they have more then one entry for each date but not in the
pivot table and the question here is how to put it in the pivot table, being
the data base his own source (if I use formulas in the data base, the files
gets extremely slow!).
The entry for 20002 on the 7th is the count of 4 entries in the data base.
So, what I need is to get the count of suppliers in the pivot table and
being the data base the source of that count.
I hope it's clear :-)


"Andrew" wrote:

Does each suppier only have one entry on your list for each date? ie - is
the entry for 20002 on the 7th = 4; or is it made up of four single entries?
If it's the former, then adding a Count of Supplier into your data field of
the Pivot table should return the desired result. If it's single entries
then I'd need to have another think about it!!

"Nelson" wrote:

Hi!
How can i insert a formula to count the suppliers per date?
The pivot table appears like this:

Date Supplier Total
07-07-2007 20002 4
20007 1
20020 10
07-07-2007 Total 15
08-07-2007 21025 2
20545 7
07-07-2007 Total 9

I need to put the count of suppliers per date.
So, the count of suppliers would be 3 on 07-07-2007 and 2 on 08-07-2007.
Thanks a lot!



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,979
Default Insert formula in a pivot table

You can add a field to the source data, as described he

http://www.contextures.com/xlPivot07.html#Unique

and use that field to count the suppliers per date.

Nelson wrote:
I thought about that also but, since it's not so dynamic, I was trying to see
all possibilities.
Thanks a lot anyway!

"Andrew" wrote:


The only thing I can think of would be to add a couple of summary formulae at
the side of the pivot table; eg:

=IF(RIGHT(A5,5)="total",A5,"") in column D,
=IF(RIGHT(A5,5)="total",COUNTA($B$5:B4)-SUM($e$5:e4),"") in column E

where column A is your date column in the pivot table, column B is the
supplier and C is the total, and the first row of data is in row 5. This
should give you summary in these columns.

Not the tidiest, but a potential solution.

"Nelson" wrote:


In the data base they have more then one entry for each date but not in the
pivot table and the question here is how to put it in the pivot table, being
the data base his own source (if I use formulas in the data base, the files
gets extremely slow!).
The entry for 20002 on the 7th is the count of 4 entries in the data base.
So, what I need is to get the count of suppliers in the pivot table and
being the data base the source of that count.
I hope it's clear :-)


"Andrew" wrote:


Does each suppier only have one entry on your list for each date? ie - is
the entry for 20002 on the 7th = 4; or is it made up of four single entries?
If it's the former, then adding a Count of Supplier into your data field of
the Pivot table should return the desired result. If it's single entries
then I'd need to have another think about it!!

"Nelson" wrote:


Hi!
How can i insert a formula to count the suppliers per date?
The pivot table appears like this:

Date Supplier Total
07-07-2007 20002 4
20007 1
20020 10
07-07-2007 Total 15
08-07-2007 21025 2
20545 7
07-07-2007 Total 9

I need to put the count of suppliers per date.
So, the count of suppliers would be 3 on 07-07-2007 and 2 on 08-07-2007.
Thanks a lot!



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Custom Formula in Pivot Table Row Cam Excel Worksheet Functions 1 January 28th 06 02:03 PM
Custom Formula in Pivot Table Row Cam Charts and Charting in Excel 0 January 26th 06 01:44 PM
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM
Pivot Table Formula carolini Excel Discussion (Misc queries) 1 December 2nd 05 11:26 PM
Formula Instead Of A Pivot Table Mike Excel Worksheet Functions 8 November 29th 05 10:50 PM


All times are GMT +1. The time now is 02:12 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"