Thank you!!! I don't have a clue how that works but it does!!! hooray.

Thanks for your help
:)

"Jacob Skaria" wrote:

Try the below array formula which uses FREQUENCY()..instead and feedback

=SUM(IF(FREQUENCY(IF(A$1:A$10=C1,MATCH(B$1:B$10,B$ 1:B$10,0)),ROW(A$1:A$10)-ROW(A$1)+1),1))

If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Hi Jacob,

I think I've figured out what the problem is....I'm not sure of the solution
though?

The formula all works fine until I have an entry in coloumn J that is
entered twice where coloumn B is different. for example,
coloumn A coloum J
1 AA
2 BA
2 BA
All works fine but if....
1 AA
2 BA
2 BA
1 BA

It starts to miss a few out?

Any ideas how to get around this?

Many thanks

Fiona

"Jacob Skaria" wrote:

No. This will be considered as two entries..Like mentioned in the previous
post even spaces count..Try out the same formula with a small set of data to
see how it works..

If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Hi Jacob,

I just had a thought, if I was counting information that had part of one
cell in another also, would the count miss it?

ie. one cell has 'roberts' the next cell has 'robertson' would it omit the
first 'roberts' thinking it was the same as 'robertson'?
Thanks

"Jacob Skaria" wrote:

Normal entered formula to count the number of distinct items in J10:H240 with
the criteria that B10:B240 = A4

=SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&""))

If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Hi,

I'm currently using the following formula:-
=CEILING(SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&"")),1)

It seems to be working, but if counting a few less items than there actually
are.

Does anyone know if there are flaws with this formula?

I've checked manually and the count if a few numbers out. The cells it is
reading are correct and I do the control, alt shift to make it work...

any thoughts would be much appreciated

thanks
fiona