Stop Pivot Table Counting NULL Strings and exclude them from pivot data summary selection

**Monkeyfud** · 08-23-2012, 06:56 AM

Hi Guys, looking for some hep with a pivot table which counts the results of cell formula

I have a data set with a number of columns with nested IF formulae to provided a value if certain conditions are met, and if not met, return a NULL STRING, i.e =If(A1=1,1,"").

I want to summarise (by counting) the results in a pivot table and then have the option to select the data values in the pivot table so that it returns the relevant data.

The problem I am having is that the pivot table is counting Null string values too. So, logically, I changed settings to Sum the values and changed the Null string to a zero, i.e. =If(A1=1,1,0). This works to summarise the data in the pivot table, summing the total of all items which meet my criteria check. However, when i select the summed data values in the pivot table (to generate a new worksheet with the relevant data to be reviewed), it returns all the data items with either "1" or "0" in the cells, rather than just the ones which are summed and excluding the cells with "0" in.

Is there a relatively simple way to get around this? So far, I can only think of copy and paste special all the formulae values (i.e. "0" & "1") in each cell and writing a macro to then CLEAR the cells containing the value "0" (or a null string depending on formulae used), which might not even work? This is beyond my skill level and not practical and the spreadsheet will be passed to very basic excel users to put their data into daily, so the formulas will need to remain to do the condition checks on new data each day.

Does anyone have any help they can provide please or a post they can pass me to?

Fingers crossed!

**npamcpp** · 08-23-2012, 07:14 AM

Hello,

set a filter on the pivot table to exclude the null strings (or the Zero values).

see attached.

Book2.xls

**JosephP** · 08-23-2012, 07:14 AM

even if the cells were blank and not included in the count, double-clicking to produce a summary sheet would still include those rows

**npamcpp** · 08-23-2012, 07:18 AM

JosephP, not if the pivot table is filtered to exclude the rows.

See attached file created in Excel 2003. There are 19 records. The pivot table shows only the records where the "report" field equals 1. This is set up with the page filter. Double click the pivot table summary field and a new sheet will open with just the fifteen valid results.

pivot1.xls

**JosephP** · 08-23-2012, 07:21 AM

@npamcpp,
true but as the asker said there were multiple columns with such formulae I didn't imagine that to be a practical solution. I may be wrong-it's happened ;-)

**npamcpp** · 08-23-2012, 07:32 AM

Even multiple columns can be included in the page filter in Excel 2003. In later versions, there is even more control.

**Monkeyfud** · 08-23-2012, 07:33 AM

Hi guys, thanks for your responses.

The filter option does not work because of the multiple colums I am summirising. If I filter blanks from one column, then that excludes then from the opportunity in the next column and so on.

What is more interesting is JospehP comment that the blank cells would be included in the Summary data regardles, which I have just tested and found to be true!

I guess this leads me to a deviation on my original question, can the summary data be changed to only return data relevant to the count in the pivot table and not return the blank data?

**JosephP** · 08-23-2012, 07:34 AM

I know but the effect would be a cumulative filter which is unlikely to be what is desired I reckon

**Monkeyfud** · 08-23-2012, 07:36 AM

Yeh, cumulative filter = Bad for me. It is interesting to note that I have spent a long time trying to solve a problem which was nto going to help me anyway witht he summary tabs! This is starting to smell like a macro...

**JosephP** · 08-23-2012, 07:37 AM

Originally Posted by Monkeyfud

I guess this leads me to a deviation on my original question, can the summary data be changed to only return data relevant to the count in the pivot table and not return the blank data?

no. you would have to filter the resulting extract

**Monkeyfud** · 08-23-2012, 07:47 AM

Ok guys, my appreciation for all your help. I am glad I posted else I would have been off on a goose chase for the rest of the day.

As an aside JosephP, if I filter my summary tab, Is there an easy way to generate an animated 3d chart with flashing text which I could then share with my work pals?

**JosephP** · 08-23-2012, 07:49 AM

yes but I suspect it may involve human sacrifice ;-)

**npamcpp** · 08-23-2012, 08:16 AM

Set up a helper column that contains the logic about the rows you want to display and returns a 1 or null string. Set up several helper columns to cater for several scenarios. Then use these helper columns in the page filter.

Stop Pivot Table Counting NULL Strings and exclude them from pivot data summary selection

LinkBack

Thread Tools

Rate This Thread

Display

Stop Pivot Table Counting NULL Strings and exclude them from pivot data summary selection

Re: Stop Pivot Table Counting NULL Strings and exclude them from pivot data summary select

Re: Stop Pivot Table Counting NULL Strings and exclude them from pivot data summary select

Re: Stop Pivot Table Counting NULL Strings and exclude them from pivot data summary select

Re: Stop Pivot Table Counting NULL Strings and exclude them from pivot data summary select

Re: Stop Pivot Table Counting NULL Strings and exclude them from pivot data summary select

Re: Stop Pivot Table Counting NULL Strings and exclude them from pivot data summary select

Re: Stop Pivot Table Counting NULL Strings and exclude them from pivot data summary select

Re: Stop Pivot Table Counting NULL Strings and exclude them from pivot data summary select

Re: Stop Pivot Table Counting NULL Strings and exclude them from pivot data summary select

Re: Stop Pivot Table Counting NULL Strings and exclude them from pivot data summary select

Re: Stop Pivot Table Counting NULL Strings and exclude them from pivot data summary select

Re: Stop Pivot Table Counting NULL Strings and exclude them from pivot data summary select

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions