Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Custom Formula in Pivot Table Row | Excel Worksheet Functions | |||
Custom Formula in Pivot Table Row | Charts and Charting in Excel | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Pivot Table Formula | Excel Discussion (Misc queries) | |||
Formula Instead Of A Pivot Table | Excel Worksheet Functions |