Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula in pivot tables involving counting text cells
I have developed a pivot table trying to count tasking assigned to various
groups/teams and also count the number that were not carried out. I can put the tasking field in the column and change to "count" BUT I can't seem to do an accurate calculation [using pivot tables, formula, calculations] on counting when I am subtracting one text field against the other. The [abridged] example of data sheet: Date Team Tasking Result 13/1/2007 1 Tasking A OK. No problems 13/1/2007 2 Tasking B Ok. 14/1/2007 2 Tasking A 14/1/2007 3 Tasking C 15/1/2007 1 Tasking D Needs more work 15/1/2007 1 Tasking C No problems This the basic idea. I have a Column counting taskings for an overall allocated tasking, not a problem. However I have tried a number of ways to get in the pivot table a field that subtracts a count of "results" from a count of "taskings", since the "results" column is only filled in if tasked done. This should give me those tasks not done. I think it is to do with the fact that these fields are text fields. I have tried to use counta formula but keep coming up will 0 for all data. I have tried other things and keeping coming up with N/A. Does anyone know how to subtract using count on text fields in pivot tables?? Thanks for your help -- Gai |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula in pivot tables involving counting text cells
Even if you've used a summary function like Count on a field in the
pivot table, e.g. Count of Tasking, the calculated field uses the sum of the underlying data. Tasking is a text field, so its sum will be zero. You could add a column to the source data, named TaskCount, and put a 1 in every row. Add another column to the source data, named TaskDone, with a formula to check for text in the Result column. For example: =IF(F2="",0,1) Then you can use these fields in the pivot table's calculated field, e.g.: =TaskCount-TaskDone Gai wrote: I have developed a pivot table trying to count tasking assigned to various groups/teams and also count the number that were not carried out. I can put the tasking field in the column and change to "count" BUT I can't seem to do an accurate calculation [using pivot tables, formula, calculations] on counting when I am subtracting one text field against the other. The [abridged] example of data sheet: Date Team Tasking Result 13/1/2007 1 Tasking A OK. No problems 13/1/2007 2 Tasking B Ok. 14/1/2007 2 Tasking A 14/1/2007 3 Tasking C 15/1/2007 1 Tasking D Needs more work 15/1/2007 1 Tasking C No problems This the basic idea. I have a Column counting taskings for an overall allocated tasking, not a problem. However I have tried a number of ways to get in the pivot table a field that subtracts a count of "results" from a count of "taskings", since the "results" column is only filled in if tasked done. This should give me those tasks not done. I think it is to do with the fact that these fields are text fields. I have tried to use counta formula but keep coming up will 0 for all data. I have tried other things and keeping coming up with N/A. Does anyone know how to subtract using count on text fields in pivot tables?? Thanks for your help -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text in Pivot Tables | Excel Worksheet Functions | |||
Pivot tables - text rather than number? | Excel Worksheet Functions | |||
Counting multiple criteria involving dates | Excel Worksheet Functions | |||
Double Counting in Pivot Tables | Excel Discussion (Misc queries) | |||
Computations involving text cells are not working?? | Excel Worksheet Functions |