Count Distinct Query

Originally Posted by jello1000

Hi,

I have three columns in a worksheet with heading; (ref#, amount, fee type). There are three different fee types, say late, standard, composite. The amounts entered correspond to the different fee types. Each entry is assigined a distinct reference# except for the composite fee. The composite fee is basically a concession where multiple standard fees can be paid but for a fixed (composite) fee. A type of discout. Where such is availed of, the equivalent corresponding standard fees are entered on sucessive lines but the fee type is assigned "C". Thereby indicating that the composite concession fee has been availed of in respect of these standard fee payments. Also the reference # is the same for each of these entries as in essence this composite fee reflects a single transaction.
My query is how can I count the number of distinct occurences of reference numbers of fee type "C". That is how can I count the number of distinct composite fee transactions. I have tried several configurations of count, countif, dcount, dcounta but to no avail.

I hope I have explained my query clearly and would appreciate your assistance in solving the above, or in offering an alternative solution.

Thanks in advance for any assistance,

J

Hi,

not very well, but I presume you want to count the number of occurances of each uniquie combination of column A and C

In D2 put

=IF(SUMPRODUCT(--(A$2:A$10=A2)*(--(C$2:C$10=C2)))=SUMPRODUCT(--(A2:A$10=A2)*(--(C2:C$10=C2))),SUMPRODUCT(--(A$2:A$10=A2)*(--(C$2:C$10=C2))),"")

and formula fill that to the end of your data, adjust row number shown as 10 to suit your data

This will show, for each 'first occurrance', the number of occurances.

hth
---