Any Excel Whizzes out there please? A little bit urgent...

[Zog]

Am Excel dunce so struggling to do the following. If I've got the following table, for example:

NAME GROUP A B C
Jane Apple 0 1 0
Mary Grape 1 0 0
Anne Apple 1 1 1
Sophie Pear 0 0 1
Jenny Grape 1 1 1
Rosie Grape 0 0 1
Beth Apple 1 1 1
Ruth Pear 0 1 0
Nina Pear 1 1 0

And I want to show a table that counts the number in each group that is >0 in each column e.g.

GROUP A B C
Apple 2 3 2
Grape 2 1 2
Pear 1 2 1

How on earth do I do it??

Thanks so much in advance!

no, she wanted to be able to add columns and for the pivot table to auto pick them up. I'm sure there is a way but it's not obvious. It makes sense if you try it with the data she's given.

You can define the the pivot table range beyond the rows where there is data, so that when data is added it is picked up automatically (after refresh). You can also have extra columns, provided you have a heading for each column.

Ah I see so when it picks up your range, you add some more columns to it yourself?

Select your range (probably include one extra column (called, say, Blank) to the right to be safe) and give it a name (Insert -> Name -> Define). Then when you are defining the PivotTable use the name of the range rather than specifying it directly. As you add extra columns in between the real data and Blank the named range will expand accordingly and the PivotTable will pick it up when you refresh it.

very clever! and quite sneaky

Might be a bit late, but what you really want to use is SUMPRODUCT.

SO:

Column A=Name
Column B=Group
Columns C-E=A,B,C.

To find out how many >0 of Group Apple, you would use the formula:

=SUMPRODUCT((B2:B10="Apple")*(C2:C10>0))

Just replace the content of the " " with the group name, and change the range you want to look at.

I cut and pasted the tables you supplied into Excel.

Assuming:

a:The source data is in the range A1:E10 (i.e. I pasted it into cell A1.)

b: the results starts a bit further down on row 13 (The word 'Group' is in A13.

=SUMIF($B$2:$B$10,$A14,C$2:C$10)

should do what you want.

SUMIF adds the numbers in a range, dependent on a criteria. The criteria is the fruit type, the range to look for the fruit type is column B and the sum range is col C (or D or E)

GROUP A B C
Apple =SUMIF($B$2:$B$10,$A14,C$2:C$10) =SUMIF($B$2:$B$10,$A14,D$2:D$10) =SUMIF($B $2:$B$10,$A14,E$2:E$10)

This works for the first row. The criteria range is an absolute reference so it won't change as you drg the forumala acorss

Apologies for typos. Bit late forme.