Pivot Table Count Question

[dixiebritt]

Customer Name

Wk1

wk2

wk3

wk4

wk5

wk6

wk7

wk8

wk9

wk10

wk11

wk12

wk13

Qtr total

Pipeline 88

8900

8900

10000

10000

=IF(SUM(B2:N2)-0,"",SUM(B2:N2)

Well 2028

Well 5783

10000

10000

=IF(SUM(B2:N2)-0,"",SUM(B2:N2)

<tbody style="box-sizing: inherit; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">
</tbody>

[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?

Qtr total	Calc	Formula	Count	Formula
=IF(SUM(B2:N2)-0,"",SUM(B2:N2)	$ 37,800.00	=SUM(B2:N2)
	$ -	=SUM(B2:N2)
=IF(SUM(B2:N2)-0,"",SUM(B2:N2)	$ 20,000.00	=SUM(B2:N2)	2	=COUNTIF(P2:P4,"<>0")

<tbody>
</tbody>

 

[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.

Count of Billed Clients	2
Row Labels	Sum of Calc
Pipeline 88	$ 37,800.00
Well 2028	$ -
Well 5783	$ 20,000.00
Grand Total	$ 57,800.00

<tbody>
</tbody>

 

[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!