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!

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!

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!

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!

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!

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