count formula for pivot table

[faizee]

I need the calculate formula for pivot table


there are numbers and "Absent" in the fields,,. so i want to count "absent"

field name is "time-in"

 

[Robert Mika]

I need the calculate formula for pivot table


there are numbers and "Absent" in the fields,,. so i want to count "absent"

field name is "time-in"

Could you post a sample of your data?

 

[faizee]

[TABLE="width: 261"]
<TBODY>[TR]
[TD]Card no[/TD]
[TD]Date[/TD]
[TD]time in[/TD]
[TD]Time out[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]1-Feb-12[/TD]
[TD="align: right"]11:23[/TD]
[TD="align: right"]19:34[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]2-Feb-12[/TD]
[TD]absent[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]1-Feb-12[/TD]
[TD="align: right"]6:44[/TD]
[TD="align: right"]20:33[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]5-Feb-12[/TD]
[TD="align: right"]9:00[/TD]
[TD="align: right"]18:22[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]6-Feb-12[/TD]
[TD]absent[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]17-Mar-12[/TD]
[TD="align: right"]8:34[/TD]
[TD="align: right"]19:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]count "absent" in (time In)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]in pivot table[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Card no[/TD]
[TD="align: right"]12-Feb[/TD]
[TD="align: right"]12-Mar[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=2></COLGROUP>[/TABLE]

 

[Robert Mika]

[TABLE="width: 261"]
[TR]
[TD]Card no[/TD]
[TD]Date[/TD]
[TD]time in[/TD]
[TD]Time out[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]1-Feb-12[/TD]
[TD="align: right"]11:23[/TD]
[TD="align: right"]19:34[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]2-Feb-12[/TD]
[TD]absent[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]1-Feb-12[/TD]
[TD="align: right"]6:44[/TD]
[TD="align: right"]20:33[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]5-Feb-12[/TD]
[TD="align: right"]9:00[/TD]
[TD="align: right"]18:22[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]6-Feb-12[/TD]
[TD]absent[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]17-Mar-12[/TD]
[TD="align: right"]8:34[/TD]
[TD="align: right"]19:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]count "absent" in (time In)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]in pivot table[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Card no[/TD]
[TD="align: right"]12-Feb[/TD]
[TD="align: right"]12-Mar[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[/TABLE]

Add another column which count the number of absent:

Excel Workbook
	A	B	C	D	E
1	Card no	Date	time in	Time out	Absent
2	11	01-Feb-12	11:23	19:34	0
3	11	02-Feb-12	absent		1
4	10	01-Feb-12	06:44	20:33	0
5	11	05-Feb-12	09:00	18:22	0
6	11	06-Feb-12	absent		1
7	10	17-Mar-12	08:34	19:00	0
8
9
10
11
12		Sum of Absent	Column Labels
13		Row Labels	Feb	Mar
14		11	2
15		10	0	0
Sheet3

 

[faizee]

ooh thats a good idea,

thanks
i just to ask, cant we directly put count or vlookup formula in pivot table --> formula--> calcuated field

 

[faizee]

is anyone here. who could help me in it..