sum range, but add a value to empty cells

[danmillion]

I have a range, say A1:A20. Some of the cells in this range are numbers, some are text (-,NA,MN, or even an error, such as #DIV/0!). I would like to create a sum of the existent number, but also add a specific number (say 99) to the cells that are text (or error, as mentioned previously)

A B <= columns
12 1
23 3
NA 1
blank NA
3 5
- 2
10 2
--------------------
345 113

=sum(12+23+99+99+3+99+10) = 345 => Rank = 1
=sum(1+3+1+99+5+2+2) = 113 => Rank= 2

The reason I am adding 99 is that I am ranking the sums/columns by the lowest sum (ascending). So higher sums, lower the rank.

Thanks for any help!!

 

[Mike LH]

Hi,

Try this


=(SUMPRODUCT(--(ISTEXT(A1:A7)))+COUNTBLANK(A1:A7))*99+SUM(A1:A7)

 

[JackDanIce]

Welcome to the board. Try:

=SUMPRODUCT(--(ISNUMBER(A1:A7)),A1:A7)+SUMPRODUCT(--(NOT(ISNUMBER(A1:A7))))*99

 

[danmillion]

Thanks for the response guys. Both solution work partially. Both formulas fails to recognize error, #DIV/0, to be more specific, but this I can prob handle it. Now, Mike, your solution is adding an extra 99 for empty cells or other text (NM, NA, -, etc). Also, if instead of 99 I used another number, the sum does not calculated correctly.
Jack, your formula works beautifully, including if I change 99 to another number! Awesome.
Quick question, can anybody explain what the "--" in front of the functions are for?

Again, thank you guys for taking the time to help me. I appreciate it.

 

[JackDanIce]

Glad it's almost working.

-- (double hyphen) turn a TRUE or FALSE value into 1 or 0 respectively. Together with SUMPRODUCT, this link is what I send people at work to help understand: Excel SUMPRODUCT formula - Syntax, Usage, Examples and Tutorial | Chandoo.org - Learn Microsoft Excel Online

You can add a test for ISERROR into the suggestion, e.g:

=SUMPRODUCT(--(ISNUMBER(A1:A7)),A1:A7)+(SUMPRODUCT(--(NOT(ISNUMBER(A1:A7))))+SUMPRODUCT(--(ISERROR(A1:A7))))*99

 

[Mike LH]

Hi,

An alternative approach

=SUMIF(A1:A7,"<1E100")+(SUMPRODUCT(--(NOT(ISNUMBER(A1:A7))))*99)


You asked about the --. in the formula the SUMPRODUCT functions are returning TRUE or FALSE and sumproduct can't evaluate these so the -- coerces TRUE into 1 and FALSE into zero.