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