+ Reply to Thread
Results 1 to 25 of 25

how to add only numbers in a row if it contains text and how to add 0 if text is found in

  1. #1
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    141

    how to add only numbers in a row if it contains text and how to add 0 if text is found in

    Say for eg.
    i want to add a1:g1 in H1, only if all the cells contain numbers. If any of the rows , contains text, like f1 d1 any text, i want the result to be 0.
    would you help

  2. #2
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    Here's one way using an array formula

    Please Login or Register  to view this content.
    Remember its an array formula so enter it with Control + shift + enter (not just enter)
    Happy with my advice? Click on the * reputation button below

  3. #3
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,714

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    Hi
    try this:

    Please Login or Register  to view this content.
    note this treats blanks as numerical value zero

  4. #4
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    141

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    Hi, Both formula works , but if you could write, how it works, for eg, crooza using 25, and nicky using istext and greater than 0, it will be useful.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    This may be easier to understand...
    A
    B
    C
    D
    E
    F
    G
    H
    1
    1
    2
    3
    4
    5
    6
    7
    28
    2
    1
    2
    3
    aa
    5
    6
    7
    0

    H1=IF(COUNT(A1:G1)=COUNTA(A1:G1),SUM(A1:G1),0)
    copied down

    COUNT() only counts numbers
    COUNTA() counts everything
    so, test 1 against the other - if they match, all entries are numbers (even numbers generated from a formula)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    141

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    Hi, i do not want count function.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    I am only using count function ti see if there is any text in that range - if there is (per your request) it returns 0, otherwise it sums teh range

  8. #8
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    Quote Originally Posted by rajuj View Post
    Hi, i do not want count function.
    Try this ...

    =IF(ISNUMBER(MATCH("zzzzz",A1:G1)),0,SUM(A1:G1))

  9. #9
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    Quote Originally Posted by rajuj View Post
    Hi, Both formula works , but if you could write, how it works, for eg, crooza using 25, and nicky using istext and greater than 0, it will be useful.


    Mine was just a quick check to see if each cell contains a number. It was quick and nasty and won't capture negatives either. The 10^25 is just a really big number. You could change the <0 to <-10^25 to capture negatives too although I'm the first to admit some of the other solutions here are probably more robust

  10. #10
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    141

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    Hi, Nicky,
    Please say, what your formula does in brief. pl.what is that multipled by 1.
    please

  11. #11
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    141

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    Hi, I think Nicky is not around. anybody gives how nickys formula works. Particularly why 1 is used and what is istext?

  12. #12
    Valued Forum Contributor
    Join Date
    01-08-2011
    Location
    Portsmouth, England
    MS-Off Ver
    Excel 2007 to 2016
    Posts
    456

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    I'll do my best

    ISTEXT returns TRUE or FALSE depending on whether a cell contains text

    The SUMPRODUCT around this looks at the array of cells A1 to G1 and returns a string of TRUE/FALSE values, depending on whether the cells contain text. So if you had say 2, aa, 3, 1, bb, 4, 5 as values in each of those cells (A1 to G1) the array would look like FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE. Multiplying by 1, changes these to values 0,1,0,0,1,0,0 so the answer is 2.

    The IF formula is checking to see if this answer (2) is greater than 0. If it is, then the result is 0. If it isn't >0, then there is no text so the numbers are added together using the SUM part of the IF formula.

    As a tip, if you ever want to see what a formula is doing, select the formula cell, go to the Formulas tab and click on the Evaluate button. You can then step through the formula as it calculates the result.

    Hope that helps your understanding a little.
    Excel is a constant learning process and it's great to help each other. If any of us have helped you today, a click on the "reputation" star on the left is appreciated.

  13. #13
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    141

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    Hi, Shirley Thanks doubly for your explanation.
    If you are excel in maths and programming , it may be easy to understand. But , when the solution is given with the notes, as you have given, would immensely benefit the users of excel who are applying the formula.
    sub query: would this work for negative numbers , or should i change slight. Please give the changed formula for that also, ofcourse, without explanation.
    Your teaching really excels. can i expect a reply

  14. #14
    Valued Forum Contributor
    Join Date
    01-08-2011
    Location
    Portsmouth, England
    MS-Off Ver
    Excel 2007 to 2016
    Posts
    456

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    Hi,

    Thanks for the positive feedback - part of my job is teaching Excel so always good to know my explanations help

    The formula will work exactly the same with negative numbers - no need to change anything. The negative numbers will just be included in the SUM part of the formula when that is applied (i.e. when no cells in the row contain text)

  15. #15
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    141

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    Hi, could you say, how the expert think of giving this thought, like coining the formula in such way that no oversight is possible, like multiplying by 1 to add and then check if it equals 0 to give the required result otherwise 0. where this trick of deep analysis in mind could be stored. Really amazing site to learn excel. With your reply, will solve this thread

  16. #16
    Valued Forum Contributor
    Join Date
    01-08-2011
    Location
    Portsmouth, England
    MS-Off Ver
    Excel 2007 to 2016
    Posts
    456

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    I'll leave that one to the forum admin

    Thanks for the feedback and happy to have helped

  17. #17
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    141

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    hi, Thanks for all who helped me to resolve.
    My appeal to forum admin to ask the experts to give solution as formulas with a little more logical explanation

  18. #18
    Valued Forum Contributor
    Join Date
    01-08-2011
    Location
    Portsmouth, England
    MS-Off Ver
    Excel 2007 to 2016
    Posts
    456

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    This is definitely one of the best sites to find out about Excel - even after many years of working with Excel I still find all sorts of new things

    When I pick up a new method or tip, I keep an example or two in a workbook so I can easily refer back to it. I include with this any explanations which have been provided - it's easy to forget things if you don't come across them very often. Also, by creating examples it helps to make the techniques stick in your mind so the next time you come across a similar problem, you will know you have a solution, even if you have to go back to your notes for the exact details.

    But if you're ever unsure, the forum is always a great place to seek help and advice

  19. #19
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    Edited version, unlike my first suggestion, this allows 0 values and empty (but not blank) cells.

    =IFERROR(SUMPRODUCT(A1:G1/1),0)

    If you want to understand how a formula works, go to the 'Formulas' tab on the Excel ribbon, then use the 'Evaluate' function.

    Complex formulas might need an explanation from the person providing it, but with simpler formulas like this, you will probably learn more from trying to figure it out yourself.
    Last edited by jason.b75; 05-28-2016 at 11:39 AM.

  20. #20
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    Here is another way with use of somewhat forgotten function ISNA()
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B C D E F G H
    1 45 text 45 text text 5 45 0
    2 45 50 45 4 45 5 8 202
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  21. #21
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    This also might work for you.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The ISTEXT examines result of the LOOKUP in the range A1:G1 and if text is found, the LOOKUP returns a text value. This makes ISTEXT return TRUE. The TRUE then returns the TRUE part of the IF function. If text is not found ISTEXT returns FALSE and the FALSE part of the IF function is returned.

    A
    B
    C
    D
    E
    F
    G
    H
    1
    1
    2
    4
    6
    5
    6
    6
    30
    2
    1
    2
    v
    6
    4
    7
    9
    0
    3
    g
    1
    2
    3
    4
    5
    6
    0
    4
    9
    8
    7
    6
    5
    4
    3
    42
    Last edited by newdoverman; 05-28-2016 at 11:30 AM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  22. #22
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    Quote Originally Posted by rajuj View Post
    Hi, i do not want count function.
    Why?

    ------
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  23. #23
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    Quote Originally Posted by rajuj View Post
    i want to add a1:g1 in H1, only if all the cells contain numbers.
    Try this array formula**:

    =IF(AND(ISNUMBER(A1:G1)),SUM(A1:G1),0)

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

  24. #24
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    Something else that just came to mind

    =IFERROR(SUMPRODUCT(SMALL(A1:G1,COLUMN(A1:G1))),0)

    Although, like others, I fail to see what is wrong with the suggestion in post #5, it's simple and it works, so why dismiss it?

  25. #25
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,714

    Re: how to add only numbers in a row if it contains text and how to add 0 if text is found

    Hi rajuj
    sorry I did not reply to your earlier thread, for some reason I am no longer receiving notifications of new comments on subscribed threads, and didn't realised you has posted a question. Looks like Shirley answered your question, though: thanks Shirley!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to find text then select RANGE form the text found to lastrow of Column J
    By tuongtu3 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-23-2014, 02:45 AM
  2. Replies: 7
    Last Post: 11-08-2013, 10:52 PM
  3. Replies: 1
    Last Post: 09-11-2013, 12:12 AM
  4. [SOLVED] If Or statement to check cell text and delete entire row if text is found
    By VBA FTW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-08-2013, 11:46 AM
  5. Replies: 1
    Last Post: 11-05-2012, 09:23 PM
  6. find text then delete two rows below it including row of found text
    By leanne2011 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-25-2011, 08:05 AM
  7. Add numbers found in text in multiple cells
    By Rhapsodie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-27-2009, 05:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1