Try .....
=SUMPRODUCT(--(LEFT(N3:N3000,3)="INC"),(A3:A3000<"")/COUNTIF(A3:A3000,A3:A3000&"")) +
SUMPRODUCT(--(LEFT(N3:N3000,2)="SE"),(A3:A3000<"")/COUNTIF(A3:A3000,A3:A3000&""))
I don't think you can OR with SUMPRODUCT (but I could be wrong!)
HTH
"ajajmannen" wrote:
Hi!!
i'm currently trying to count the number of unique numbers in a Column
using this formula:
=SUMPRODUCT((A3:A3002<"")/COUNTIF(A3:A4002;A3:A3002&""))
So far so good.....But I want to add some conditions like only count
the numbers that meet the condition say N1:N3000="SE*"
I tried a couple of things but nothing seem to work can you please help
out.....Don't laugh and I will paste my own failed solutions:
=SUMPRODUCT((A1:A3000<"");(AND(OR(N1:N3000="SE*"; N1:N3000="INC*")/COUNTIF(A1:A3000;A1:A3000&""))))
On this one I get #VALUE! and i can't figure out why.
I have also tried to put the AND/OR condition before the actual
starting of the array of the SUMPRODUCT but nothing.
--
ajajmannen
------------------------------------------------------------------------
ajajmannen's Profile: http://www.excelforum.com/member.php...o&userid=34130
View this thread: http://www.excelforum.com/showthread...hreadid=538999