distinct count Formula help

All

I'm having an issue with doing a distinct count.

I have a field that represents an account number and it has letters and numbers, so it's a string. I have the field grouped and anytime the account number appears more than once, I want it to be counted once.

example of data

group

3/29/16    lisdexamfetamine    Z921936
3/22/16    guanFACINE ER       Z921936

I want to count that account number 1 per group


I created a variable that's a number that will return a 1 if there's an account number or a zero if not but that won't let me summarize either

3/29/16    lisdexamfetamine    Z921936   1
3/22/16    guanFACINE ER       Z921936   1
4/1/17     texctrune                     0

not sure what else to try...any help?


Edited by mkhenneman - 11 Apr 2017 at 5:28am

Are you trying to summarize a summery, if so, then you cannot do that unless you create your own running totals in formulas.

I do believe that's the problem. Do you have an example how you would create this work around? thank you.

I did not read specifically what you are trying to achieve.  But generally you have three formulas.  One to reset the shared variable (you could use global variables but they do not work for me for some reason), i.e., shared numbervar counter := 0;  normally in the report header or a group header. Second the calculation;  shared numbervar counter;  if someconditon the counter := counter +1;  Then the third formula to either display the value or use it in another formula.

you can also consider a formula that concatenates the group field value with the account number and do a distinct count of that (at both group and report levels).