# IF Statement

Can anyone help me with this IF Statement.

I'm trying to say that if \$I13:\$I2250 is equal to "", or "NOT DEFINED", do not count.

=IF(SUMPRODUCT((\$I\$13:\$I\$2250<>""), IF(SUMPRODUCT((\$I\$13:\$I\$2250<>"NOT DEFINED"))/COUNTIF(OR((\$I\$13:\$I\$2250,\$I\$13:\$I\$2250&"","NOT DEFINED")))))
IrogSinta

``=COUNTIFS(\$I\$13:\$I\$225,"<>""",\$I\$13:\$I\$225,"<>"NOT DEFINED")``

IrogSinta =COUNTIFS(\$I\$13:\$I\$225,"<>""",\$I\$13:\$I\$225,"<>"NOT DEFINED")
is counting the blank rows
Change it to this:
``=COUNTIFS(\$I\$13:\$I\$225,"<>",\$I\$13:\$I\$225,"<>"NOT DEFINED")``

The change is still counting the rows with "NOT DEFINED"

=COUNTIFS(\$I\$13:\$I\$225,"<>",\$I\$13:\$I\$225,"<>"NOT DEFINED")
Correction:
``=COUNTIFS(\$I\$13:\$I\$225,"<>",\$I\$13:\$I\$225,"<>NOT DEFINED")``

I may need to open up another ticket, because I needed the unique values also, which is why I was using the product in the original formula. So, if I had the following data in Column I, the count would be 6 unique items.

Again, I'm trying to count all the unique items in the column that are not blank, and not text "NOT DEFINED".

COLUMN I

NOT DEFINED
LE
LF
L2
L2
NOT DEFINED
NOT DEFINED
L3
LA
LV
LV
LV
Ah, I didn't understand from your original question that you needed this to be unique.  Sorry, but I'm not sure I could help there.
mark_harris231

membership
Create an account to see this answer
Signing up is free. No credit card required.
This was heavily based on the formula presented here:

Office.Microsoft.Com / Count Unique Values

My primary enhancement was the addition of the SUBSTITUTE functions to handle the "NOT DEFINED" entries.

Also, this approach might choke on larger data sets (I didn't test).  If that's the case, you may need a VBA solution.