Inconsistent counts in Excel Pivot

[rob737]

Hi Forum

Hope you can help I have an issue with Pivot tables in Excel.
Issue: I down load a workbook from another application. When I create a pivot table it counts inconsistently as follows:

Column A = 56

Col B = 32 Valid Fields 24 Blanks
Col C = 37 Valid Fields 19 Blanks
Col D = 15 Valid Fields 41 Blanks

When I create a Pivot table it counts as follows

Col B = 56
Col C = 56
Col D = 15

My understanding is B and C are correct, it counts valid cells and blank cells it’s just making a count.

If I filter all the blank cells in B and C and hit “Delete” the pivot counts only the valid cells
If I filter D and insert a space in the blank cells the pivot table counts all cells (56).

This report is very large and “doctoring” a row to get a consistent count is very time consuming.
Hope someone can help explain this and give me a formula, Macro etc. to fix (preferably row D).

Many Thanks
Regards
Rob

 

[sheetspread]

I down load a workbook from another application.

Are you sure they're actual blanks and not "" strings? Test with =isblank().

 

[rob737]

Hi Forum

Really sorry I just noticed the column that is counting just the valid fields is a date, the column that counts all cells is just text. Not too sure if there is a way around this without "Deleting" all the blank fields so it just counts cells with Text. Any suggestions would be great.

Regards
Rob