Calculated Pivot Field

[Sir Babydum GBE]

Hi again

Say I have a data sheet with sales agents. Each row contains the name of the agent, his manager, how many apples sold, and how many oranges sold.

Could I get a pivot table to return the names of the managers, along with a count of agents working under him that sold apples, and a count of agents that sold oranges.

What's making this tricky is that an agent may appear a few times in the data sheet.

Thanks

[Bob Phillips]

That's simple enough BD.

Drag the agent name to row fields pane, the manager name to the Page fields, and the apples and oranges to the data Items pane fields. Excel will automatically assume SUm for them.

[Sir Babydum GBE]

True, but I don't want a sum of apples and oranges - but a count of agents that have sold one or more - so I don't want to see any agent names either.

The Manager needs to be in the Row fields.

[Bob Phillips]

Do you want a count of agents by apples and by oranges, or just a count?

And do you really mean > 1 or > 0?

[Sir Babydum GBE]

Bob

You're right, I spotted the > 1 and amended it - sorry about that.

So Manager1 may have had 10 agents. over the week, all of them sold some apples, but only half of them sold oranges. Because the worked 5 days, each name may appear multiple times

But i'd want the pivot table to show:

Manager1 10 5

You've given me a formula that calculates unique items in a list with multiple conditions. But on one of my workbooks (with many records) the formula is taking a very long time to calculate, so I'm investigating the possibility of using a pivot table on that workbook.

Thanks

BD

[Bob Phillips]

BD,

You could add a simple hrlper columns wit formula of

=--(C2>0)

and

=--(D2>0)

and just add those fields to the data pane.

One thing that might get in its way. Can Say a manager of Sir BD, an Agent of Bob appear with many (more than 1) entry for Apples say?