Confusion on Grouping and Counting

[mrob]

Hi All,

Hoping for some help on this one.

I am trying to determine if I can convert certain data within Excel 2003 on XP to give me the total number of policies per representative (not individual units) on a spreadsheet. (Some policies have multiple clients on them but, I want just to have a count of the policies, not individuals on them,)
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I tried to tinker with the group and outline tools, as well as the pivot table, but I am only seeing each policy number entry, it counts each unit even though naturally some policies are “group” policies. <o></o>

Is there a way to do this?<o></o>

Thanks in Advance

 

[GlennUK]

Hi mrob, and welcome to the board.

I would use an array formula ( entered using Ctrl-Shift-Enter ) using FREQUENCY or inverse COUNTIF, like:
=SUM(1/COUNTIF(A1:A99,A1:A99))
entered using Ctrl-Shift-Enter ( Excel will place curly braces around the formula when you do this, to indicate that it's an array formula ).

Also, see here:
http://office.microsoft.com/en-us/e...nique-values-in-a-data-range-HP003056118.aspx

 

[mrob]

Glenn, Thank you for your response, as soon as I can get off this other assigment, I will try out your suggestion and let you know how it works.