Display text depending on one or more cell with value

For column A:

=IF(COUNTIF(A:A,">0")=1,"Text when One value is found",IF(COUNTIF(A:A,">0")>1,"Text for More than one","Text for None"))

Well, you are missing some commas:

=IF(COUNTIF($D$2:$D$41,">0")=1,$B$2:$B$41,IF(COUNTIF($D$2:$D$16,">0")>1,"Apparel Collection",IF(COUNTIF($D$17:$D$41,">0")>1,"Accessories Collection",IF(COUNTIF($D$2:$D$41,">0")>1,"Collection",""))))

But this part won't work:

$B$2:$B$41:

You need to extract the specific value from that range - Perhaps this, array entered (enter using Ctrl-Shift-Enter)

=IF(COUNTIF($D$2:$D$41,">0")=1,INDEX($B$2:$B$41,MATCH(TRUE,$D$2:$D$41>0,FALSE)),IF(COUNTIF($D$2:$D$16,">0")>1,"Apparel Collection",IF(COUNTIF($D$17:$D$41,">0")>1,"Accessories Collection",IF(COUNTIF($D$2:$D$41,">0")>1,"Collection",""))))

Still, you would be better off describing what you have and what you want rather than posting a failed formula.....

Not perfectly - I think you should check it this way (again, array-entered), which is better logic, at least to me:

=IF(COUNTIF($D$2:$D$41,">0")=1,INDEX($B$2:$B$41,MATCH(TRUE,$D$2:$D$41>0,FALSE)),IF(AND(COUNTIF($D$2:$D$16,">0")>1, COUNTIF($D$17:$D$41,">0")>1),"Collection",IF(COUNTIF($D$2:$D$16,">0")>1,"Apparel Collection",IF(COUNTIF($D$17:$D$41,">0")>1,"Accessories Collection",""))))