Need formula to get one value from a column that is either a number, or a N/A, or a blank

[elivergara]

So, I have a table with rows filled with scores, N/A, 0 (zeroes), and blanks, on different rows (depending on the row value in column A). Only one value appears in each column.

Since these values are scattered in different rows, I have one row above this table that sums each entire table column, to bring these values into one row.
I then plan on using these values on a chart.
The problem is, Excel takes the N/A's and outputs a 0 as the result of the =SUM(B2:B30) for example. and it also returns 0 as the sum of the blank rows.

I only want it to bring up the value on that cell, not sum it, but I don't know what formula would do that.

I have tried to use an IF statement, (if the sum <> 0 show the value, else show " "), and that works to an extent, but it doesn't deal with N/A's, blanks and zeroes. I need zero scores to count, but N/A or blanks to show that value also, so I already know =SUM is not the right formula.

What can I use instead?

Thanks!

 

[elivergara]

Here's an example of what I am trying to achieve. Thanks!

 

[alvisonhunter]

Hello Eli,

From what I saw in your screenshot, what you are trying to achieve is to get the totals (sum) of each columns for the not empty or error values. Well with MS Excel versions of 2010 and up you can take advantages of the AGGREGATE Function to make these calculation. In this particular escenario, the formula that I can suggest you to add or type in into cell B14 is the following:

=AGGREGATE(9, 6, B3:B12)

If you need more information about this formula, please click here to read the documentation. I certainly hope this solution comes handy to what you were looking for.
Greetings from Nicaragua!

 

[Fluff]

How about
=IFERROR(LOOKUP(2,1/(B3:B12<>""),B3:B12),"")

 

[elivergara]

How about
=IFERROR(LOOKUP(2,1/(B3:B12<>""),B3:B12),"")

Thanks, this worked great because it shows the N/A when it is there, but when the cell is blank, it still returns 0 and I need zeroes to show as zero because a zero score is not the same as a no-results. Dooes that make sense?
Thanks!!

 

[Fluff]

That's what it does.

+Fluff New.xlsm
	A	B	C	D	E	F	G
1
2		score 1	score 2	score 3	score 4	score 5	score 6
3			100
4
5				80
6							0
7
8						N/A
9
10
11
12
13
14			100	80		N/A	0
Main

Cell Formulas
Range	Formula
B14:G14	B14	=IFERROR(LOOKUP(2,1/(B3:B12<>""),B3:B12),"")

 

[elivergara]

That's what it does.

+Fluff New.xlsm
	A	B	C	D	E	F	G
1
2		score 1	score 2	score 3	score 4	score 5	score 6
3			100
4
5				80
6							0
7
8						N/A
9
10
11
12
13
14			100	80		N/A	0
Main

Cell Formulas
Range	Formula
B14:G14	B14	=IFERROR(LOOKUP(2,1/(B3:B12<>""),B3:B12),"")

Thank you very much!!! this worked!! I truly appreciate it!

 

[Fluff]

You're welcome & thanks for the feedback.