Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to create a PivotTable that will display the average of the count of rows for each item category/subcategory. To be specific, let's say I have the following list of fruit consumption:

Orange 12-Jan
Orange 12-Jan
Orange 13-Jan
Banana 12-Jan
Banana 13-Jan

I'd like to know the average number of each type of fruit that were eaten per day. In this case that would be 3 oranges / 2 days = 1.5 oranges / day and 2 bananas / 2 days = 1 banana / day. My file has about 1500 rows and about 10 types of fruit over some 100 days.

How could I get a PivotTable that looks something like

Orange 1.5
Banana 1
Apple  ...

and so on? I could live with a PivotTable that contained other additional fields if that was necessary.

I've been messing around with this for a while and I'm starting to get frustrated. I'd happily accept an embarrassingly trivial answer.

Thanks a lot.

share|improve this question
up vote 5 down vote accepted

No pivot table needed. Order by fruit type, use the Excel Subtotal functionality, and have it count by fruit type. You'll end up with a list like this:

      FRUIT    DATE
      Apple    12-Jan
      Apple    13-Jan
 Apple subtotal     2
      Orange   12-Jan
      Orange   13-Jan
      Orange   13-Jan
 Orange subtotal    3

Then simply divide each subtotal by the total number of days.

mtone's answer shows how to calculate total number of days.

share|improve this answer
@mtone: Agree. Automation is good, I went for quick-and-dirty :) The only downside to your method is that if a new fruit is introduced, you would have to set up the calculation for the new fruit. So that part of it isn't automated, where using the sort & subtotal functions will natively handle new fruit types. – DWong Feb 1 '10 at 23:49
Sorry, I had deleted my previous comment after re-reading and agreeing. – mtone Feb 2 '10 at 1:38
Thanks to both, you've provided very useful suggestions. – alexsome Feb 2 '10 at 13:56

PivotTables are not well suited for that sort of stuff. The fundamental problem is your final calculation: 3 oranges / 2 days. How in the world can Excel figure out that you want to divide by 2 days? You have to tell it somehow, and PivotTables can't really do such "vertical" comparisons.

I suggest you use regular formulas to achieve your goal.

A1 Fruit    B1 Date
 2 Orange     01-12
 3 Orange     01-12
 4 Orange     01-13
 5 Banana     01-12
 6 Banana     01-13

A10: Daily Orange Intake
B10 (DaysElapsed): =MAX(B2:B6)-MIN(B2:B6) +1
C10 (OrangeConsumed): {=SUM((A2:A6="Orange")*1)}
D10 (DailyOrangeIntake): = E3/E2       -> Result: 1.5

*C10 is an array formula to count how many oranges in your list (technique taken from cpearson article). CTRL+Enter to enter it as such.

You could automate the final results even further by having an array formula list all individual fruits on a separate columns (see Listing distinct elements in a list), and then copying the C10 formula above next to each cell. These series of formulas could update themselve nicely as you add new data to your spreadsheet. Basically just like a pivot table.

share|improve this answer
Or could be accomplished with SUMIF. The great part about this is the comment to not use a pivot table. – DaveParillo Feb 2 '10 at 0:13

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.