countif - lookup across

[stoke]

I have a large set of data which is grouped, each line has a group label column A (apple, orange, banana etc), each group could have zero or many line entries.

some lines have data in column J and some don't.

i want to look up and count the number of entires in colum J for each group (apple, orange, banana etc).

can you give me the formula to do this please?

many thanks,

 

[ETID]

Look in the help on database functions....you want the dcount function in this case.

 

[stoke]

Thanks, this doesn't have the desired result i want to be able to sum all the instances of data in colume J that match against a given group name.

i have got as far as this:
=IF(ISNA(VLOOKUP($E2,range,COLUMNS(range),FALSE)),"-",VLOOKUP($E2,E2:L2,COLUMNS(range),FALSE))

which returns the value from the correct column - what i would like to do is count the number of instances of this value against each group name.........

hope this makes sense

 

[ETID]

Have you tried the pivot table?

 

[stoke]

That works well, Thank you.
i was just hoping to to automate the process a stage further.

 

[ETID]

Run the macro recorder while defining the pivot table,..then you can edit the code to make it more automatic.