Pivot table question

[dixiebritt]

I have a pivot table to count the number of clients billed per quarter. The quarters only have a number in the total if they had Billings ; otherwise it is blank. I have one branch who had billing in q1 , but none in the remaining. However my pivot shows the same number billed in q1 in the remaining. Why would this count “blank” cells? Hope I am explain this correctly

 

[AFPathfinder]

What's the formula you are using? Does this seem to occur with other clients who aren't billed every quarter?

 

[dixiebritt]

=IF(SUM(Q2:AC2)=0,"",SUM(Q2:AC2))

if there is no data in Cells Q2:AC2, it leaves it "" (blank). however, my pivot table is still showing "5" clients billed for that quarter, although the result is blank. Hoep that makes sense?

 

[AFPathfinder]

If it's missing numbers, I'd say check the cells to make sure none of them are formatted as text. If it's adding extra numbers, check for hidden columns, cells that may have a white font, or formulas that may have been changed due to inserting/deleting cells.

 

[dixiebritt]

I did - and they are set as Accounting and still not working

 

[AFPathfinder]

Is each row a quarter or do you have a COUNTIFS/SUMIFS formula that is subtotaling the quarters?

 

[dixiebritt]

Column A (has company names) Columnns Q-AC, have their billings and AD - is the formula to add the billings for each client. If they didn't have billing for quarter, AD is empty. I want the pivot table to only count the number of clients who had billings for the quarter.