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

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