Needing Help With a Formula

[Sooner4Life]

Hello,

I'm needing help figuring out a way to tweak a formula that I use on a spreadsheet at work. I use the formula below to track active unique projects and it works fine as long as my only criteria is not counting blanks..

=SUM(IF(FREQUENCY(IF(ISNUMBER(1/Borger!T3:T5000),IF(Borger!P3:P5000<>"",MATCH(Borger!P3:P5000,Borger!P3:P5000,0))),ROW(Borger!P3:P5000)-ROW(Borger!P3)+1),1))

But I've recently added the option to include other catagories in these tabs that I don't want included in the total count ("R&M" and "PWRS") as these are not projects.

=SUM(IF(FREQUENCY(IF(ISNUMBER(1/Borger!T3:T5000),IF(AND(Borger!P3:P5000<>"",Borger!P3:P5000<>"R&M",Borger!P3:P5000<>"PWRS"),MATCH(Borger!P3:P5000,Borger!P3:P5000,0))),ROW(Borger!P3:P5000)-ROW(Borger!P3)+1),1))

I thought the solution would be to include an AND function in the IF statement and then add two more criteria excluding both keywords but it does not seem to work as intended. Any ideas?

Thanks.

 

[AhoyNC]

Try:
AND function will not work in this case as it will only return one value of TRUE or FALSE where you need an array.

[TABLE="width: 1602"]
<colgroup><col width="1602"></colgroup><tbody>[TR]
   [TD="width: 1602"]=SUM(IF(FREQUENCY(IF(ISNUMBER(1/Borger!T3:T5000),IF(Borger!P3:P5000<>"",IF(Borger!P3:P5000<>"R&M",IF(Borger!P3:P5000<>"PWRS",MATCH(Borger!P3:P5000,Borger!P3:P5000,0))))),ROW(Borger!P3:P5000)-ROW(Borger!P3)+1),1))[/TD]
 [/TR]
</tbody>[/TABLE]

 

[Sooner4Life]

Man thank you so much I appreciate it that worked perfect! I've been trying to figure this out going on two days!!

 

[AhoyNC]

You're welcome. Thanks for the feedback.