Ignoring text and errors with Sumproduct

[Guest]

I have a ranking formula for numerical values in Column J based on Text
categories in Column A:

=SUMPRODUCT(--(A$13:A$2651=A13),--(J13<J$13:J$2651))+1

It doens't work because some of the cells in Column J include error or text
values.

How can I make this formula work so that it ignores these error/text values?
Thanks!

 

[Harlan Grove]

SteveC wrote...

I have a ranking formula for numerical values in Column J based on Text
categories in Column A:

=SUMPRODUCT(--(A$13:A$2651=A13),--(J13<J$13:J$2651))+1

It doens't work because some of the cells in Column J include error or text
values.

How can I make this formula work so that it ignores these error/text values?

The best way would be to eliminate the error values in col J. Filtering
out text is simple:

=SUMPRODUCT(--(A$13:A$2651=A13),--ISNUMBER(J$13:J$2651),--(J13<J$13:J$2651))+1

Filtering out error values can't be done without using the IF function,
and IF requires that formulas calling it be entered as array formulas
when IF should return array results. If you can't eliminate the error
values in col J, you'll have to use the array formula

=SUM((A$13:A$2651=A13)*IF(ISNUMBER(J$13:J$2651),J13<J$13:J$2651))+1

 

[Guest]

Thanks Harlan. After playing around wtih it some more, I realized there was
an #N/A error in Cell A2651, which was bringing #N/A errors for all the cells
where the formula below was pasted. Once I fixed CellA2651, I had no
problems. I'm going to keep your formulas as a backup in case I get further
errors... thanks a lot for your time.
-SteveC

 

[Guest]

Hey, this is what I'm doing now:

=IF(ISNUMBER(J13),(SUMPRODUCT(--($A$13:$A$2650=$A13),--(J13<J$13:J$2650))+1),1000000)

If the cell contains an error or text, I assign it a high number (1000000)
so it is ranked at the very bottom of the screen.

do you like this version?

I'm using it in conjunction with this to assign % rank, where the formula
above is in column AM.
=IF(AM13/COUNTIF($A$13:$A$2500,A13)>1,1,AM13/COUNTIF($A$13:$A$2500,A13))

See a follow up question in a separate post if you are interested -- trying
to figure out how to easily use autofilter to show only the top 10%, 20%
etc...

thanks again for your comments/help!