• 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.

Formula error need to solved

W

webmax

Member
Hi

I have 4 datas to sum (ie Total)
Mark 1
Mark 2
Mark 3
Mark 4
I want to total this 4 Marks. In that the Mark 3 has #N/A i want to show total of rest of the values. How to do that

I am attaching the sample files
 

Attachments

  • formulas.xlsx
    9 KB · Views: 5
Hi Webmax,

Below are the recommendations:

If the numbers are always postive then try:
=SUMIF(B2:E2,">0",B2:E2)

If the numbers can be +ve or -ve try:
=SUM(IF(ISNUMBER(B2:E2),B2:E2))
Note this will be array formula; so must be entered with Ctrl+Shift+Enter

If you are using Excel 2010 or Excel 2013 then you can also try:
=AGGREGATE(9,6,B2:E2)

Regards,
 
hi ur formula is working fine if the data is continuous

But if sum up the different range (not continous) it is not working

I am attaching the file for your reference
 

Attachments

  • formulas.xlsx
    9 KB · Views: 5
@webmax

Try below formulas:

For any version:
Array formula must be entered with Ctrl+Shift+Enter:
=SUM(IF($A$1:$H$1="Sales",IF(ISNUMBER($A2:$H2),$A2:$H2,0),0))

For version 2007+ , Normal formula:
=SUMIFS($A2:$H2,$A$1:$H$1,"=Sales",$A2:$H2,">0")

Regards,
 
You must log in or register to reply here.
Back
Top