Adding text with SUM function then return error

[DashboardNovice]

I am summing a range with the SUM function. If any cell in the range contains a text, I want the entire answer to return an error, but instead a cell with text is just being ignored and all other cells with values are still being added.

I know I can do this by explicitly referencing each cell then using a plus sign, but I want to use the SUM function and a range.

So if any cell in the SUM range contains a text value, the entire SUM formula should return an error.

 

[NARAYANK991]

Hi ,

Try using SUMPRODUCT instead of SUM , as in :

=SUMPRODUCT((A1:A5)*1)

where A1 through A5 has your data , some of which might be text.

You can use SUM instead of SUMPRODUCT , but you will then have to enter it as an array formula , using CTRL SHIFT ENTER.

Narayan

 

[DashboardNovice]

Hi ,

Try using SUMPRODUCT instead of SUM , as in :

=SUMPRODUCT((A1:A5)*1)

where A1 through A5 has your data , some of which might be text.

You can use SUM instead of SUMPRODUCT , but you will then have to enter it as an array formula , using CTRL SHIFT ENTER.

Narayan

Thank you. I have had a change in plans. Rather than using SUM, I want to use AVERAGE. So if any cell in the range to Average has a text value, return an error.

Is there a way to do this?

 

[NARAYANK991]

Hi ,

The same , except that you use AVERAGE instead of SUM , and you have to enter it using CTRL SHIFT ENTER , as in :

=AVERAGE((A1:A5)*1)

Narayan

 

[DashboardNovice]

Hi ,

The same , except that you use AVERAGE instead of SUM , and you have to enter it using CTRL SHIFT ENTER , as in :

=AVERAGE((A1:A5)*1)

Narayan

Wow! That is awesome. I know that doing CTRL + SHIFT + ENTER is a way to enter an array formula which automatically puts the { } in the formula.

But can you explain how this works?

 

[NARAYANK991]

Hi ,

The principle is quite straightforward ; since the AVERAGE or SUM function itself ignores text values when averaging or summing , we need to introduce a mathematical operation to generate the error ; when a text value and a number are added , subtracted , multiplied or divided , Excel will return an error value ; you need to take care that when multiplying or dividing use 1 for the number , and when adding or subtracting use 0 for the number.

The second point you need to remember is that when the SUM or AVERAGE function is used on its own , you do not need to array enter it , since when you pass an array to the SUM function , as in =SUM(A1:A5) , Excel knows the parameter is an array , and returns the correct result. But when you carry out a mathematical operation as in :

=SUM((A1:A5)*1)

you need to array enter it because the first operand is an array , while the second is a scalar value , just 1. If you do not array enter it , Excel will carry out the multiplication only on A1 , and not on the others ; once you array enter it , using CTRL SHIFT ENTER , Excel does A1*1 + A2*1 + A3*1 + A4*1 + A5*1 ; where any of the cells contains text , this mathematical operation will generate an error.

Narayan

 

[DashboardNovice]

Hi ,

The principle is quite straightforward ; since the AVERAGE or SUM function itself ignores text values when averaging or summing , we need to introduce a mathematical operation to generate the error ; when a text value and a number are added , subtracted , multiplied or divided , Excel will return an error value ; you need to take care that when multiplying or dividing use 1 for the number , and when adding or subtracting use 0 for the number.

The second point you need to remember is that when the SUM or AVERAGE function is used on its own , you do not need to array enter it , since when you pass an array to the SUM function , as in =SUM(A1:A5) , Excel knows the parameter is an array , and returns the correct result. But when you carry out a mathematical operation as in :

=SUM((A1:A5)*1)

you need to array enter it because the first operand is an array , while the second is a scalar value , just 1. If you do not array enter it , Excel will carry out the multiplication only on A1 , and not on the others ; once you array enter it , using CTRL SHIFT ENTER , Excel does A1*1 + A2*1 + A3*1 + A4*1 + A5*1 ; where any of the cells contains text , this mathematical operation will generate an error.

Narayan

Very nice. And very well explained. Thank you sir. I will have to review your post a few more times, but I do understand the idea. Thank you again!

 

[Geosele]

Hi ,

Try using SUMPRODUCT instead of SUM , as in :

=SUMPRODUCT((A1:A5)*1)

where A1 through A5 has your data , some of which might be text.

You can use SUM instead of SUMPRODUCT , but you will then have to enter it as an array formula , using CTRL SHIFT ENTER.

Narayan

Hi: I have a similar error issue but I am adding from non-array like =A1+B1+C1+D1 where simplistically A1=2, B1=6, C1=19 and B1="N" (for info "N" is text denoting None Attendance at a Race Category). How would the SUM or SUMPRODUCTS formula look like in this case?

 

[vletm]

Geosele​

Yes ... You have a similar issue and
You've posted Your issue to someone else thread ... as well as it's few years old.
You should open a new tread for You.
( As You've read from Forum Rules ).
This thread is closed now.