SumProduct to Count decimal or Text values in a table column

[drom]

Hi and thanks in advance!

I am using a table
I my table in one of the columns

• Decimal numbers are NOT allowed (data validation = easy)
• Text values are not allowed (easy)

I am using conditional formatting and works OK

• =OR(ISTEXT(I13);(INT(I13)-I13)<>0)

In My eg: Table $I$13:$xxx is named rcAAA

• xxx could be $5012 if my tables has 5000 rows

Now I would like to use a SumProduct to count how many cells are NOT OK
Using SumProduct to Count decimal or Text values in on rcAAA

I am using;

• =SUMPRODUCT(--((rcAAA<=0)+((INT(rcAAA)-rcAAA)<>0)+(ISTEXT(rcAAA))>0);--(rcAAA<>""))
  • But Does not work if a cell keeps Text on rcAAA for some unjustified reason

Thanks again

 

[barry houdini]

Try an array formula like this:

=SUM(IF(rcAAA<>"",IF(ISTEXT(rcAAA),1,IF(MOD(rcAAA,1)>0,1))))

confirm with CTRL+SHIFT+ENTER