Pivot Table / Chart Ignore / Exclude blank "" cells created using IF formula

Hi all,

I've been looking online for a solution to this for a couple of hours now with no luck.

I have an if formula that will show text based on project dates. The if formula checks to see if there is a date entered in Column B (Today's Date) and then check that against the project's due date.


The problem I am having is that in my Pivot chart on "Results" sheet, the blank cells with the "" result are being counted as blanks.

I've looked online and some people have has success with use #N/A or NA() instead of "" , but that isnt working for me.


I also saw some suggestions online to use a VBA code, however this workbook will not support VBA as it will be used in a multi-user SharePoint online space.

Attached is a sample of workbook. Any help will be appreciated. In the meantime will try to see why I cant upload the workbook

Hello,

you are using an Excel table as the source for the pivot chart. Excel tables automatically apply formulas to new rows of data, so there is no reason whatsoever to pre-fill unused rows with formulas that deliver a blank or an #NA. Just delete these empty rows and refresh your pivot tables.

If you insist on this unfortunate design, you can always edit your pivot table and filter out the unwanted elements. Click the filter button and untick the boxes for the data you don't want to include.

pivotfilter.jpg

cheers, teylyn

system glitch

if you insist of having it the way you currently have
you could potentially use a named range as dynamic data source


create named range - in example i have used PVTDATA
make it refer to =OFFSET(InputSheet[#Headers],0,0,COUNTA(Entry!$B:$B))

change all pivot data data sources to be PVTDATA (instead of the current INPUTSHEET)

this formula COUNTA(Entry!$B:$B) controls how many rows to take into account
if you have other items in column B you can either deduct them manually COUNTA(Entry!$B:$B) - how many items you have