When I enter a letter, the function does not work

    .......................A .. ... B ......C......D........E......... F...... G..... H..... I
    1 - Expenses ..-10.....-15.... -15L...........-10K......-5.......................
    2 - Sales .........25T....50.......15L........................25C....................
    3 - Totals:........15......25.......35.....0.....-10........-20.....................

    Problem #1:


    When I enter a letter, the function does not work. When a Letter is entered in the Expenses Row (negative numbers only), it does not accept the letter - the letter simply disappears. When I enter a letter in the Sales Row (positive number), it throws off the sum function - the addition comes out incorrect. In the totals Row, A3, I have the following function: =IF(COUNTA(A1:A2),SUM(A1:A2), "")


    The reason I have this as the function, rather than a simple SUM function, is so that the Totals line does not show "0" until data is entered in the corresponding cells. But, I'm sure there are many other ways to obtain the same result.


    Basically, when I enter a Letter in the Sales Row, I need to multiply that sales amount time .12, or 12 percent - and then subtract the expenses and the commission from the sales number and have that appear in the Totals Row. But, not all columns will have letters and those numbers will just be added without commission fees showing.


    Data will be derived from this simple chart on different sheets to show; total sales, total sales by each person, total commission, total expenses, etc…
    Any suggestions? If you happen to have a formula, can you also explain Why the formula works - I'm trying to learn the language of Excel - Thank you!

    Re: When I enter a letter, the function does not work


    Please read the Forum Rules and pay more attention to the thread Title.


    Hiow do you expect help when you don't expalin which function or if it's a UDF then post the code.

    Re: When I enter a letter, the function does not work


    Hey RoyUK - I'm not sure which Forum Rules you are refering to that you think I broke - and, I'm not sure what else I could have said in my thread title. I don't even know what a udf code is... I tried to explain the function I need as clearly as possible.


    You see, I'm new to Excel and it's language - I'm just getting started. That's why I'm here.


    The function I need is to be able to sum A1 and A2 - minus the 12 percent commision IF the sales line has a letter in it, and put that total in A3...does that help? But, I already have a funtion in A3 that keeps a "0" from appearing if there is not data in A1 and A2, and I posted that above. I'm trying not to have to deal with code or vba at this point - it's a little too advanced for me.

    Re: When I enter a letter, the function does not work


    Well your title is so vague. Which Function are you talking about, it's not obvious from your title or original post.


    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.


    To attach a file to your post, you need to be using the "Reply to Thread', not the "Quick Reply" button.


    On this page, below the message box, you will find a button labelled 'Manage Attachments'.
    Clicking this button will open a new window for uploading attachments.


    You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
    Alternatively you can click the Attachment Icon to open this page.


    To upload a file from your computer, click the 'Browse' button and locate the file.


    To upload a file from another URL, enter the full URL for the file in the second box on this page.
    Once you have completed one of the boxes, click 'Upload'.


    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

    Re: When I enter a letter, the function does not work


    Hi Rai77,


    You try to add 2 apples with 5 pears.
    It can be so but still can be known exactly how many letters can be entered after that number, if you enter letters and numbers before etc. ..

    Re: When I enter a letter, the function does not work


    When you add a letter like that it is no longer a number so number functions won't work. You could use a Custom NumberFormat

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login