+ Reply to Thread
Results 1 to 12 of 12

Is it possible for 1 cell to total 1 column if populated, but if not to add up other cells

  1. #1
    Registered User
    Join Date
    04-11-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    6

    Is it possible for 1 cell to total 1 column if populated, but if not to add up other cells

    Hello

    I am about to tear my hair out! Help would be much appreciated.

    I am designing a cost worksheet for my business and what I am trying to achieve is the following:

    Cell I37 is the cell I wish the forumla to be placed in

    Formulas and values have been entered elsewhere on the sheet which will result in values being calculated in numerous cells in colum I - ( I4 to I36). When these cells have figures in them I want cell I37 to simply add them up to give a total. However (this is where I am stuck) if there are no values in these cells (which could ocur if the other cells they rely on have no figures in them), I want cell E37 and H37 to add up instead - and the result shown in cell I37.

    So basically if there are no figures for the cell I37 to add up in I4 to I36, then it is to add up two other cells - so there is always a total shown in I37.

    Can anyone advise?

    Many thanks

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Is it possible for 1 cell to total 1 column if populated, but if not to add up other c

    do the cells in I4:I36 return 0 or "" when there are no data values?
    if they return "" you could use
    =if(count(I4:I36),sum(I4:I36),SUM(E37,H37))
    or you may prefer
    =if(SUM(I4:I36),SUM(I4:I36),SUM(E37,H37))
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Registered User
    Join Date
    04-11-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Is it possible for 1 cell to total 1 column if populated, but if not to add up other c

    Hi

    The cells show blank for some reason - I think they had " in before but somehow I got rid of that.

    But I have tried the bottom one and it works!

    Thank you for helping so quickly!
    I was getting nowhere with this - I think I was adding them up with plus symbols to which yours doesn't

    My last thing I am struggling with is a cell containing the VALUE error.

    I have used the following formula: =IF(ISBLANK(E5),"",SUM(I5-E5))


    I did this so it didnt show a zero or dashes I think if the other cells it refers to are not populated. But if I put something in one of the cells it refers to but not the other I get this error. Is there a way of saying the above but basically not to worry if I5 doesn't have somethng in - just continue to show nothing in the cell - and only show something if there is figures in both I5 and E5?

    Thanks

    Claire

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Is it possible for 1 cell to total 1 column if populated, but if not to add up other c

    try
    =IF(COUNT(E5,I5)=2,I5-E5,"")

  5. #5
    Registered User
    Join Date
    04-11-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Is it possible for 1 cell to total 1 column if populated, but if not to add up other c

    Est Cost Actual Cost Sup Inv No Variance Est Profit Sold At Act Profit
    5,000.00 4,900.00 100.00 1500.00 6,500.00

    Hi

    This is the table basically.

    That forumla stops the error value showing if I enter nothing in E5, but if I do put something in it the Actual Profit cell - (which is where that formula is) doesn't show anything - it is not adding up E5 and I5

    E is the actual cost
    I is the sold at figure

    Cheers

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Is it possible for 1 cell to total 1 column if populated, but if not to add up other c

    it only calculates if there are numbers in both cells-isn't that what you want?

  7. #7
    Registered User
    Join Date
    04-11-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Is it possible for 1 cell to total 1 column if populated, but if not to add up other c

    Hi

    Yes thats right - but its not calculating at present, and there are values in E5 and I5

    I5 and all I for that matter - has the following formula in - =IF(ISBLANK(H5),"",SUM(D5+H5))

    I'd used the same for the J column (actual profit), but it was showing the error if I did not enter something in E and I. There are times when E may have something in (actual cost) but I does not (as instead of itemising each cost with a sold at value - instead we may enter all the costs seperatly but just add profit once - to the total cost rather than per item if that makes sense).

  8. #8
    Registered User
    Join Date
    04-11-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Is it possible for 1 cell to total 1 column if populated, but if not to add up other c

    Gosh - I am sorry - ignore that! I was putting it in row4!!

    Gosh I think its been too long a day!

  9. #9
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Is it possible for 1 cell to total 1 column if populated, but if not to add up other c

    are you sure those cells contain actual numbers and not text that looks like numbers? can you provide a workbook?

    if not try
    =IF(AND(E5<>"",I5<>""),I5-E5,"")

  10. #10
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Is it possible for 1 cell to total 1 column if populated, but if not to add up other c

    :-)

    nearly quitting time though I reckon

  11. #11
    Registered User
    Join Date
    04-11-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Is it possible for 1 cell to total 1 column if populated, but if not to add up other c

    me again.....

    Ive uploaded the sheet Im taking about - that would help explain my madness!

    Im trying it out for different scenarios - and one attached is if at quoting stage and only have estimate costs etc in - quoted client, but no actual confirmed costs which we would enter at time of receiving invoices or placing orders.

    The issue is that J37 - the actual profit cell shows an error profit as I've copied the formula over from one of the others you suggested to the J column, but it is showing a 6k actual profit when in fact it should show nothing until actual cost has been entered by way of auto sum of the actual cost column once is has been filled in.



    Ta
    Attached Files Attached Files

  12. #12
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Is it possible for 1 cell to total 1 column if populated, but if not to add up other c

    if we can assume that you can never have 0 actual costs legitimately then perhaps just
    =IF(SUM(J4:J36),SUM(J4:J36),IF(E37>0,I37-E37,""))
    if you want it blank-or replace the "" with 0 if you prefer to see 0

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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