Pivot Table with Where clause?


K

Ken

Hi all. Hope you're having a great holiday!

But back to business :).

Let's say I have a worksheet with three columns: Employee, Assignment
Name, and Complete (Y/N). There is a one-to-many relationship between
Employee and Assignment Name. I understand how to make a Pivot table
to show the total number of assignments for each employee, but how
would I make a pivot table showing for each employee the number of
assignments where Complete = Y?

Thanks!

Ken
 
Ad

Advertisements

D

Debra Dalgleish

Add the Complete field to the Page area of the pivot table. From the
dropdown, choose Y, and the pivot table will be filtered for those
records only.
 
K

Ken

Thanks for that info.

What if I had a date column called StartDate, and I only wanted to
include items where the StartDate value was greater than or equal to
today's date? The page field drop down doesn't seem to support
something like that. Note that I am using Excel 97 SP2.

Thanks for any info again!

Ken
 
D

Dave Peterson

I'd add another column to the raw data.

Call it something nice, but put a formula like:

=if(a2>=today(),"Yes","No")

Then drag that to the page field. Then you can choose to show the yes's, no's
or both.
 
D

Debra Dalgleish

You could add a column to the table, and calculate if the StartDate
meets the criteria. For example if StartDate is in cell A2: =A2>=TODAY()

Add this new field to the PivotTable page area, and filter for TRUE.
 
K

Ken

Thanks to you and to Dave who also responded to my question. You both
came up with the same solution--and it worked like a charm.
Excel-lent (sorry about that, couldn't help myself--still must have
residue egg nog in me :) ).

Thanks again,

Ken
 
Ad

Advertisements

D

Dave Peterson

Yeah, but mine was 5 minutes earlier!!

(hehehe)
Thanks to you and to Dave who also responded to my question. You both
came up with the same solution--and it worked like a charm.
Excel-lent (sorry about that, couldn't help myself--still must have
residue egg nog in me :) ).

Thanks again,

Ken
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top