Count unique numbers with an AND condition????

[ajajmannen]

Hi!

I Have another thread from earlier and I am working on trying to expand a formula which was created from help in this forum http://www.excelforum.com/showthread.php?t=538999

So i'm now hoping for the same great help as I got the last time.

So I have this formula:
=COUNT(1/FREQUENCY(IF((LEFT(TOTUNDERLAG!H2:H4000;3)="#WO");IF(TOTUNDERLAG!T2:T4000="ABD";IF(TOTUNDERLAG!A2:A4000<>"";TOTUNDERLAG!A2:A4000)));IF((LEFT(TOTUNDERLAG!H2:H4000;3)="#WO");IF(TOTUNDERLAG!T2:T4000="ABD";IF(TOTUNDERLAG!A2:A4000<>"";TOTUNDERLAG!A2:A4000)))))
Where it should count th unique numbers in Column A if it meets the condition LEFT(#WO) in column H. But I want to add an AND condition to the first IF statement. like this:
=COUNT(1/FREQUENCY(IF(AND(LEFT(TOTUNDERLAG!H2:H4000;3)="#WO";RIGHT(TOTUNDERLAG!V2:V4000;4)="#FRO");IF(TOTUNDERLAG!T2:T4000="ABD";IF(TOTUNDERLAG!A2:A4000<>"";TOTUNDERLAG!A2:A4000)));IF(AND(LEFT(TOTUNDERLAG!H2:H4000;3)="#WO";RIGHT(TOTUNDERLAG!V2:V4000;4)="#FRO");IF(TOTUNDERLAG!T2:T4000="ABD";IF(TOTUNDERLAG!A2:A4000<>"";TOTUNDERLAG!A2:A4000)))))
But then again as the last time I started the last thread the formula returns 0. I have tried to play around with the number of ( ) but it doesn't matter.

Please help.

for the help these are the columns it should look for:
Column A
> PROBLEM_ID
> 1499986
> 1499986
> 1499986
> 1499986
> 1758434
> 1929352
> 1929352
> 1936837
> 1936837
Column V
GROUP_ID
ETD-I-SEDCSFRO
ETD-I-SEDCSFRO
ETD-I-SEDCSQA
ETD-I-SEDCSFRO
ABD-SEOSSFROQA
ABD-SEOSSFROQA
ABD-SEOSSINFFRO
ETD-I-SEDCSFRO
ABD-SEOSSINFFRO

Column H
ACTIVITY_ACTION_ID
#STATUS
#WORKING
#STATUS
#STATUS
#WORKING
#STATUS
#WORKING
#STATUS
#WORKING


This together should give the result 2 when also Column T is ABD.
But it returns 0 for a reason I can't seem to manage on my own.

thanks for the help?

[olasa]

Det är inte ofta man ser en landsman. Jag tror dom flesta drar sig för att kasta sig över en så lång och komplicerad formel...vi tar det på engelska.

Ok, so if I understand correctly, COUNT the number of unique number that meets the criteria:
Old formula: ((A2<>"")*(LEFT(H2;3)="#WO")*(T2="ABD"))
New formula: ((A2<>"")*(LEFT(H2;3)="#WO")*(T2="ABD")*(RIGHT(V2;4)="#FRO"))


I think this will work,
=SUMPRODUCT(--(FREQUENCY(A2:A40*(A2:A40<>"")*(LEFT(H2:H40;3)="#WO")*(T2:T40="ABD")*(RIGHT(V2:V40; 4)="#FRO");A2:A40*(A2:A40<>"")*(LEFT(H2:H40;3)="#WO")*(T2:T40="ABD")*(RIGHT(V2:V40; 4)="#FRO"))>0))-1

Hope it works
Ola

Note: Ctrl+Shift+Enter is not needed.
I enclose a test sample + a useful link

[ajajmannen]

Hi!!

I tried it a few times and then my priorities went in another direction so thats why I haven't replied.......
After actually building up the formulla from scratch according to your suggestion in the post it really worked like a charm.

Tack så mycket...och som sagt det ska vara en Svensk till att vara räddaren i nöden

ohh by the way this was the finished product:
=SUMPRODUCT(--(FREQUENCY(FEUNDERLAG!A2:A27000*(LEFT(FEUNDERLAG!H2:H27000;3)="#WO")*(RIGHT(FEUNDERLAG!V2:V27000;3)="FRO")*(FEUNDERLAG!T2:T27000="ABD");FEUNDERLAG!A2:A27000*(LEFT(FEUNDERLAG!H2:H27000;3)="#WO")*(RIGHT(FEUNDERLAG!V2:V27000;3)="FRO")*(FEUNDERLAG!T2:T27000="ABD"))>0))-1