I have created a rollup worksheet to summarize a number of other tables and worksheets. The rollup is comprised of a pivot table and a static listing below of other counts and sums. It is necessary to break up the data into pivot and non-pivot views, as some of the calculations being accomplished in the data set require a portion of the data to be removed from the main worksheet the pivot table is based on and isolated into separate worksheets for unique calculations.

The static listing counts and sums based on the filter applied in the pivot table. I am accomplished this by:

1. Create the pivot table
2. Use a filter (right now, the filter is a calculated cell in the main set of data that does a VLOOKUP on an account number (alpha numeric) and returns the name of a branch location.
3. NAME the pivot table filter cell "LocationName". I also created a report header in which the corresponding account number is returned for the filtered data being viewed. This is named "LocationAccountNumber"
4. In the static listing, use COUNTIF and SUMIF functions to report values for the account numbers that match "LocationAccountNumber"

This clumsy way of doing the calculation works as long as the account number being represented by "LocationAccountNumber" is in the worksheet being totaled. This, however, is not always the case. If the COUNTIF or SUMIF do not see any data when trying to match for the "LocationAccountNumber", Excel returns #VALUE error.

My account numbers reside in Column C of each worksheet. I need a function that will first check the data sheet column C for the account number being filtered and, if not present, stop the calculation. This would be something like a conditional IF statement that is false for the LocationAccountNumber to return "" and keep the cell blank. Since IF statements require the syntax order of CONDITION, VALUE IF TRUE, VALUE IF FALSE, this won't work.

I'm sure this is easy...I'm not looking at this the right way.

Thanks in advance.