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!

not sure if there's a quicker way, but here how I have just done it........

1. Delete out all zero's
2. Select all of the data, click Data,Sort,by Group
3. Select all data, click Data,Subtotal, at each change in Group, Use function "Count", Add subtotal to A,B,C
4. Click OK
5. A margin will be created on the left hand side of your worksheet, click on 2 within this to hide the individual totals and hopefully you will get the numbers you are after.

Sorry if its not too clear.

Oh thank you! That works, but is there a way I can set it up to do it automatically if the data changes IYSWIM? So if there was a column D and I added in the data at a later date, I want it set up so it will calculate the info for me automatically (don't ask for much do I )

I'm pretty sure there is, by setting up a database. I'll think some more.

Have you got microsoft access?

Sadly not.

havee you looked into pivot tables
think that would be the way to do it but id need to have a try

can you send the spreadsheet to me if i give you an address?

You need a Pivot Table

LOL, slow, I was doing it here

Typed a response there that disappeared, sorry.

Yes, suspect pivot table is the way to go but how can I get it to count >0 rather than add the numbers together?

Sharpmolarbear, thank you for your very kind offer but it's full of highly confidential stuff, hence my apples and pears example . Could you use that?

you can change whether it uses sum/count
right click and go to pivot table wizard (I think) should be one of the options in there

I did wonder why you were so obsessed with the number of apples and pears - maybe a 5-a-day initiative

I was going to suggest Count or CountNums, but it counts the zeros.

yuou could do a countif
or create a fn if that doesnt exist
but i think a pivot table would be easier

is there a countif?? Well you know what I'm getting at

Can you stick in some hidden columns that show 1 if the corresponding cell is >0 but "" if it is 0, then run the pivot table on that?

I think DCOUNT might work, either that or COUNT with nested if statements, but I don't have time to play with it, sorry!

Oh fantastic, thank you all

I'm going to sit down and have a play with this after tea - will report back

please do - have had a quick look at pivot tables and it's certainly not obvious to me how it would be done, I'll have another look later

You will be pleased to know that a pivot table worked beautifully!

Thank you all once again

well done you!

ooh how? oi couldnt get the abc bit in!
dont worry if you're busy

sorry could get it in but would have to select each time, wouldnt add new ones automatically iyswim

No, you're right, you'd have to reselect each time, but at least it worked

I use pivot tables all day long.I am not sure what you mean by reselect, but have you tried right-clicking on the pivot table, then "Refresh Data"?