• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUM in Excel without Error

G

Geosele

New Member
Hi: I read an old thread about SUM or SUMPRODUCTS like doing =SUM((A1:D1)*1) etc.
I am adding from non-array cells in an excel worksheet using simple =A1+B1+C1+D1.It works when A1 to D1 are all numbers but when one of the cell eg B1 is denoted as a text "N" (to represent None Attendance for the D1 event), I get an error as attached snapshot.
If possible, how would one use the SUM or SUMPRODUCTS formula versions to accept the N events without triggering an error in in the answer [this case?
NNN20:00:0009:30:001020:00:0009:30:001020:00:0009:30:001020:00:0009:30:001020:00:0009:30:001020:00:0009:30:0010#VALUE!10
 

Attachments

  • #VALUE Error Msg.png
    #VALUE Error Msg.png
    10.4 KB · Views: 11
Belleke said:
Code: 
=SUM(A1:D1)
Click to expand...
Hi: Excuse me, my error in query....I'm not actually adding Role 1 from A to D. Yes, I am adding in same row but can be A1+C1+F1+P1 etc ie random but same row items in mixture of Number (2 or 30 0r 15 etc) and Text ("N") in this case. Hope this make sense. Please assist if you or others can.
 
Like this?
Edit @ p45cal, It does, I forgot to post it:eek:
 

Attachments

  • tellen.xlsx
    14.3 KB · Views: 8
Geosele said:
Hi: I read an old thread about SUM or SUMPRODUCTS like doing =SUM((A1:D1)*1) etc.
I am adding from non-array cells in an excel worksheet using simple =A1+B1+C1+D1.It works when A1 to D1 are all numbers but when one of the cell eg B1 is denoted as a text "N" (to represent None Attendance for the D1 event), I get an error as attached snapshot.
If possible, how would one use the SUM or SUMPRODUCTS formula versions to accept the N events without triggering an error in in the answer [this case?
NNN20:00:0009:30:001020:00:0009:30:001020:00:0009:30:001020:00:0009:30:001020:00:0009:30:001020:00:0009:30:0010#VALUE!10
Click to expand...
To handle the scenario where cells may contain both numbers and the text "N," you can use the following array formula with the `IF` function:

```excel
=SUM(IF(ISNUMBER(A1:D1), A1:D1, IF(A1:D1="N", 0, 0)))
```

This formula checks if each cell is a number using `ISNUMBER`. If it is a number, it includes that value in the sum; if it's "N," it considers it as zero; otherwise, it also considers it as zero. Remember to enter this formula as an array formula using Ctrl + Shift + Enter.

This way, you can accommodate both numeric values and the "N" text without triggering an error.
 
Hello Debsar.

Please let me know what causing error in detail based on problem statement

Thank you
 
Probably missing something but AFAIK SUM does not care about text whereas summing in the usual way returns an error
 
You must log in or register to reply here.
Back
Top