Pivot table report help

Hi there,

I have a simple pivot table set up which gives out a summary-level report. Please see the attached file. We would like to have standardized columns and they are:

Reporting quarter Reporting period Site CCM targets total encounters.

The current problem I have is that the values in the "total encounters" column of the pivot table are the same as the "CCM targets" column, which are NOT desirable.

The filter on CCM target is set to "Yes", which is shown with no problem. But I also would like to show a column of value that represents the total number of encounters, so essentially including "yes" and "no" in the "total encounters" column

Is this possible with a pivot table? Or anything other than a pivot table can do the job?

Any comment/feedback is much appreciated.

Thank you

Originally Posted by tlafferty

Take a look at the SUMPRODUCT function:

Please Login or Register  to view this content.

See attached workbook.

Hi,

Thank you.

If the list is on-going, in other words, rows will increase on a weekly basis, how should I go about making the change in the formula to accommodate that?

Please see the attached:

I tried to do...

Please Login or Register  to view this content.

and I get #N/A errors in column B in the summation table below. So what I mean is....is there a formula that tells it to always look for the next available value? or this is getting into VBA?

Thank you

Originally Posted by tlafferty

You're getting the #N/A errors because your two arrays are of different size. $C$2:$C$110000 is much larger than $D$2:$D$8. Also, the way you have your report laid out, your formulas in the table are overlapping the results, so I'm not sure how your actual data is laid out.

Hi,

I found a way to achieve what I had originally intended to do and it's actually not using pivot table.

However, I'm still having some trouble getting it to work. Could you take a look at it please?

Basically, I'm trying to use a summary-table to pull data from two worksheets within the same workbook by using SUMPRODUCT, but it's partially working and I am not sure how to fix the problem. It's also asking me to update values when I open or save because of missing links, which is strange...

Formula is this:

Please Login or Register  to view this content.

If you get rid of the second SUMPRODUCT, it's working, but not if you add the two together. Notice that I'm using customized ranges, whose scope is all set to Workbook.

Thank you

Thank you Latterfy, problem solved.

I wasn't very careful about naming my worksheet.