Link to home
Create AccountLog in
Avatar of mato01
mato01Flag for United States of America

asked on

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")))))
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

How about:
=COUNTIFS($I$13:$I$225,"<>""",$I$13:$I$225,"<>"NOT DEFINED")

Open in new window

Avatar of mato01

ASKER

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")

Open in new window

Avatar of mato01

ASKER

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")

Open in new window

Avatar of mato01

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of mark_harris231
mark_harris231
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
Avatar of mato01

ASKER

This solution was perfect for what I was trying to do. Thanks
My pleasure.  Thanks for the grade.