Hi all, 

First post here, tried to find my answers, but didn’t get a complete solution for my issue, so hope you can help.

I have to count no of distinct trial numbers in a range in column A based on 2 criteria. 

I want the trialnumber only to be counted if:

criteria 1: the part rated in column D is a leaf (either leaf1, leaf2, leaf3) and not a plant

criteria 2: there is a value in column z (so no empty cell)

Using the information found in the community, I currently managed to get my distinct trial numbers using this formula:  =SUM(1/COUNTIF($A3:$A15;$A3:$A15))

I also managed to get counts based on it being a leaf using this formula:  =COUNTIF($D3:$D15;”LEAF*”)

But I’m not managing to get the 2 combined so that he only counts the distict value if it is a leaf, let alone to add the additional criteria that there must be a value in column Z 

Thanks for your help

PS. Probably it would be useful to have my file, but I’m not sure how I need to add this to the post.

​Hi all,  First post here, tried to find my answers, but didn’t get a complete solution for my issue, so hope you can help. I have to count no of distinct trial numbers in a range in column A based on 2 criteria. I want the trialnumber only to be counted if:criteria 1: the part rated in column D is a leaf (either leaf1, leaf2, leaf3) and not a plantcriteria 2: there is a value in column z (so no empty cell) Using the information found in the community, I currently managed to get my distinct trial numbers using this formula:  =SUM(1/COUNTIF($A3:$A15;$A3:$A15))I also managed to get counts based on it being a leaf using this formula:  =COUNTIF($D3:$D15;”LEAF*”) But I’m not managing to get the 2 combined so that he only counts the distict value if it is a leaf, let alone to add the additional criteria that there must be a value in column Z  Thanks for your help PS. Probably it would be useful to have my file, but I’m not sure how I need to add this to the post.  Read More

​