Pivot Table Count Question

[dixiebritt]

[TABLE="width: 960"]
<tbody style="box-sizing: inherit; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">[TR]
[TD]Customer Name
[/TD]
[TD]Wk1[/TD]
[TD]wk2[/TD]
[TD]wk3[/TD]
[TD]wk4[/TD]
[TD]wk5[/TD]
[TD]wk6[/TD]
[TD]wk7[/TD]
[TD]wk8[/TD]
[TD]wk9[/TD]
[TD]wk10[/TD]
[TD]wk11[/TD]
[TD]wk12[/TD]
[TD]wk13[/TD]
[TD]Qtr total[/TD]
[/TR]
[TR]
[TD]Pipeline 88[/TD]
[TD]8900[/TD]
[TD]8900[/TD]
[TD][/TD]
[TD]10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(SUM(B2:N2)-0,"",SUM(B2:N2)
[/TD]
[/TR]
[TR]
[TD]Well 2028[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Well 5783[/TD]
[TD][/TD]
[TD]10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(SUM(B2:N2)-0,"",SUM(B2:N2)[/TD]
[/TR]
</tbody>[/TABLE]

[FONT=&quot]This mock spreadsheet is an example of what I am running currently, with the formula in Qtr Total, the way i have it set. I want the cell to remain "blank" if there was no billing in Wks of the quarter. With that said, I have a pivot table, where I want to count the number of billed clients for the Quarter. If we use this as my example and run the pivot table counting the number of billed clients, the result is still showing 3. is there a way to prevent this from only counting those clients who had billing numbers? [/FONT]

 

[reberryjr]

What is your expected result? Is it "2"?

 

[dixiebritt]

Yes.

 

[reberryjr]

What I notice is, you're trying to count the number of Customer Names that have sales > $0.00. I would think that you would want your Qtr Total column to be the sum of Wk1 through Wk 13. With that in mind, I did this. Does this work for you?

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]Qtr total
[/TD]
[TD]Calc
[/TD]
[TD]Formula
[/TD]
[TD]Count
[/TD]
[TD]Formula
[/TD]
[/TR]
[TR]
[TD]=IF(SUM(B2:N2)-0,"",SUM(B2:N2)
[/TD]
[TD] $ 37,800.00
[/TD]
[TD]=SUM(B2:N2)
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] $ -
[/TD]
[TD]=SUM(B2:N2)
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=IF(SUM(B2:N2)-0,"",SUM(B2:N2)
[/TD]
[TD] $ 20,000.00
[/TD]
[TD]=SUM(B2:N2)
[/TD]
[TD="align: right"]2
[/TD]
[TD]=COUNTIF(P2:P4,"<>0")
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dixiebritt]

because my data is so large - will the pivot table not extract this data? or do i need to use the COUNTIF formula?

 

[reberryjr]

The answer depends on the need really. If you need to display the week over week billing, then the format you have, with the CountIF is probably the best option. If you only need to show the Clients Billed and Total Billed, then I would do a Pivot table, with a cell above it that shows the count.

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Count of Billed Clients
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels
[/TD]
[TD]Sum of Calc
[/TD]
[/TR]
[TR]
[TD]Pipeline 88
[/TD]
[TD] $ 37,800.00
[/TD]
[/TR]
[TR]
[TD]Well 2028
[/TD]
[TD] $ -
[/TD]
[/TR]
[TR]
[TD]Well 5783
[/TD]
[TD] $ 20,000.00
[/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD] $ 57,800.00
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dixiebritt]

Thank you. I figured out my issue int he pivot table. but this works too, if i wanted to incorporate it on the document. thank you again

 

[reberryjr]

Awesome!