IF Function - Avoiding cells with text to find sum

**chesapeake_catskills** · 03-05-2015, 05:01 PM

Hey all,

I'm trying to find the sum across two columns of information.

Column A contains cells that either contain text or numerical value.
Column B contains either blank cells or numerical value.
Column C would contain the sum.

This is a two part problem for me:

1) I want to find the sum of both columns if both columns have numerical values within them.
2) But if Column A has text in the cell, then I want the value of Column B to translate over to Column C.

For the first problem, I am using: =IF([@[ColumnA]]>0&[@[ColumnB]]>0,SUM([@ColumnA]]+[@[ColumnB]]),[@[ColumnB]])
This finds the value of both columns if they both contain numbers. But if Column A does contain text, then I'm receiving #value.

Any help is much appreciated!

**FDibbins** · 03-05-2015, 05:04 PM

Hi welcome to the forum

aa...1...1
3...2...5
4...bb...4

SUM(A1:B1) copied down works for me?

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

**chesapeake_catskills** · 03-05-2015, 05:15 PM

Wow I can't believe the ":" between columns did it. Thank you!

**FDibbins** · 03-05-2015, 05:48 PM

":" (without the "") is used to define a range, so if the range goes from A1 to J10, it would be defined as A1:J10, or, in your case E5:F5

I see you are using what is called a Structured Table. These can be very useful and do some automatic stuff for you, but if you are not very familar with formula syntax and structures, that can make things overly confusing.

For instance, in a non-structured table - ie just any old table of rows and columns that you put together - you sould use this formula...
=SUM(E5:F5)

But in a structured table (like you have), that same formula becomes...
=SUM(Table1[[#This Row],[Lbs. Leftover]:[Yield - lbs.]])

You can still use Structured Tables with the "simpler" formula, you just have to type in in yourself, instead of using the mouse

**FDibbins** · 04-25-2015, 11:22 AM

Thanks for the feedback

IF Function - Avoiding cells with text to find sum

LinkBack

Thread Tools

Rate This Thread

Display

IF Function - Avoiding cells with text to find sum

Re: IF Function - Avoiding cells with text to find sum

Re: IF Function - Avoiding cells with text to find sum

Re: IF Function - Avoiding cells with text to find sum

Re: IF Function - Avoiding cells with text to find sum

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Avoiding text in an array formula when using the SUM function

Avoiding double-count of cells with similar text

Help avoiding error in this range find macro.....

Avoiding a Nesting If function

Avoiding Cells

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions