--If A4 is a text string check out whether the text strings in B10:B240 is
exactly same. (no leading, trailing spaces )

--Any formulas returning a space " " instead of "" blank

--Try out the same formula in a small set of manually entered values..to see
that it works fine.

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


"Fiona Yorke-Saville" wrote:

Thanks Jacob,

I still seem to be a few out. Would I have to change the format of the
cells or something like that? Your formula did work, but the problem I had
is still the same.



"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