# Count unique numbers with an AND condition????

1. ## Count unique numbers with an AND condition????

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.

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?

2. ## Unique number count

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

3. ## You are the king of Excel

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1