Conditional Processing in Pivot Table

[benesch]

Hi:

I am responsible for producing a spreadsheet showing the number of Assisted Living Facilities by county that have been completed, are pending approval, or are under construction, and the corresponding number of beds. This information is updated four times a year. I am currently doing so using a Pivot Table in an MS Access 97 form. The number of facilities in each category is calculated using the COUNT function, and the number of beds are grouped using the SUM function. My data source is a DBASE III file that is provided to me by another organization. In certain cases, an existing facility adds new beds, in which case, I do not want to double count the facility, but I still wish to include the beds. Does anyone know of a method to exclude certain records from the COUNT function in a Pivot Table, but still include the beds for those records in the subtotals for each category using the SUM function?

I would be most grateful for any help that you can provide. I have posted this thread in the MS Access forms forum as well, since my PIVOT TABLE is stored as an Access Form. I am including it here to reach EXCEL users. My apologies to those who may encounter this thread twice.

 

[DaleWatson123321]

Hi benesch,

Let's be optomistic that someone with expertise in the use of Excel Pivot Tables will be able to come up with a solution.

However, if it turns out that the variation you hope to achieve is NOT possible with Pivot Tables, I thought I should point out another option that WILL work.

I've assisted other Tek-Tips members with analysis of data downloaded from mainframe databases, using Excel's "database functions". Using these functions, it's possible to generate a "unique" list, and then use this list to generate a matrix of totals for whatever is required to be added. Based on the "criteria" you specify, you can generate totals by whatever time period you require.

If and when you want to consider this option, let me know.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca