SOLVED

Don't count empty cells in pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-114468%22%20slang%3D%22en-US%22%3EDon't%20count%20empty%20cells%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-114468%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20data%20exported%26nbsp%3Binto%20an%20XML%20file%20which%20I%20map%20into%20excel.%20Then%20I%20convert%20this%20data%20into%20a%20pivot%20table%20to%20get%20the%20total%20count%20necessary%20columns.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20excel%20file%2C%20I'd%20like%20to%20get%20a%20count%20of%20%22Trades%22%20column%20only%20if%20the%20corresponding%20cell%20in%20the%20column%20%22Clashes%22%20is%20filled%2Fnot%20empty.%20But%20using%20the%20pivot%20table%20it%20counts%20the%20value%20even%20if%20the%20cell%20is%20empty.%20How%20do%20I%20achieve%20this%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20in%20the%20file%2C%20%2207.NetOps%22%20is%20completely%20empty%20but%20the%20pivot%20table%26nbsp%3Bgives%20a%20count%20%221%22%20for%20it.%20Is%20there%20any%20way%20to%20ignore%20the%20cell%20for%20counting%20if%20the%20%22Clash%22%20%26amp%3B%20%22Active%20Clash%22%20columns%20are%20empty%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-114468%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharts%20%26amp%3B%20Visualizing%20Data%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Epivot%20table%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-114479%22%20slang%3D%22en-US%22%3ERe%3A%20Don't%20count%20empty%20cells%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-114479%22%20slang%3D%22en-US%22%3E%3CP%3EAnwesh%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eadd%20a%20column%20%22Empty%3F%22%20to%20your%20table%20with%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%3DCOUNTA(C2.E2)%3D0%3C%2FPRE%3E%3CP%3EDrag%20the%20fileld%20into%20the%20report%20filter%20and%20filter%20for%20FALSE.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

I have a data exported into an XML file which I map into excel. Then I convert this data into a pivot table to get the total count necessary columns. 

 

In the attached excel file, I'd like to get a count of "Trades" column only if the corresponding cell in the column "Clashes" is filled/not empty. But using the pivot table it counts the value even if the cell is empty. How do I achieve this? 

 

For example, in the file, "07.NetOps" is completely empty but the pivot table gives a count "1" for it. Is there any way to ignore the cell for counting if the "Clash" & "Active Clash" columns are empty?

1 Reply
Best Response confirmed by Anwesh Gangula (Contributor)
Solution

Anwesh,

 

add a column "Empty?" to your table with this formula:

=COUNTA(C2.E2)=0

Drag the fileld into the report filter and filter for FALSE.