+ Reply to Thread
Results 1 to 11 of 11

Calculate only if one out of multiple blank cells are filled out

  1. #1
    Registered User
    Join Date
    11-24-2013
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    7

    Calculate only if one out of multiple blank cells are filled out

    Good day!

    My name is Platak and I am currently working on some spreadsheets, surprise! Even though I am somewhat 'handy' with regards to Excel coding, I do find myself lost getting to certain outcomes and would like to utilize this forum for those moments. Thanks for all the help in advance!

    Problem:
    I would like the 'calculating' cell (C1) to only calculate when one out of the two variable cells (A1 and B1) contain data.

    My attempt:
    C1: =IF(A1,B1="","",A1+B1)

    This does not seem to work. I have tried the search function on this forum which did not quite lead me to find this particular problem. Your help is much appreciated!

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Calculate only if one out of multiple blank cells are filled out

    Try this: If(or(A1="",B1=""),"",A1+B1)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    11-24-2013
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Calculate only if one out of multiple blank cells are filled out

    That is exactly it! Thank you.

  4. #4
    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: Calculate only if one out of multiple blank cells are filled out

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    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

  5. #5
    Registered User
    Join Date
    11-24-2013
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Calculate only if one out of multiple blank cells are filled out

    Thanks FDibbins, done and done. Even though this thread is marked solved, I have yet another and similar riddle for those who know the answer to it.

    Problem:
    I want the calculating cell (C1) to display whether the two variable cells (A1 and B1) contain data by means of text.

    Example:
    - If A1 contains data but B1 does not, C1 should display: "Please Check B1".
    - If B1 contains data but A1 does not, C1 should display: "Please Check A1".
    - If both A1 and B1 do not contain data, C1 should display: "Please check A1 and B1".

    Thanks in advance!

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Calculate only if one out of multiple blank cells are filled out

    =if(And(A1="",B1=""),"Please Check A1 and B1",if(and(A1<>"",B1=""),"Please check B1",If(and(A1="",B1<>""),"Please check A1","")))

  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: Calculate only if one out of multiple blank cells are filled out

    slightly shorter...
    ="Please check "&if(And(A1="",B1=""),"A1 and B1",if(and(A1<>"",B1=""),"B1",If(and(A1="",B1<>""),"A1","")))

  8. #8
    Registered User
    Join Date
    11-24-2013
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Calculate only if one out of multiple blank cells are filled out

    That works great. Thank you!

  9. #9
    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: Calculate only if one out of multiple blank cells are filled out

    Happy to help and thanks for the feedback

  10. #10
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Calculate only if one out of multiple blank cells are filled out

    @Ford--Like the amendment.

  11. #11
    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: Calculate only if one out of multiple blank cells are filled out

    @ Alan, thanks, you did all the work though

+ 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. Conditional formating multiple cells based on blank or filled single cell.
    By turbotank in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-13-2012, 12:11 PM
  2. Collecting data from filled cells and skipping blank cells
    By KellieB in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-20-2012, 09:45 PM
  3. Excel 2007 : Blank Cells to be filled up
    By sushil10s in forum Excel General
    Replies: 6
    Last Post: 02-10-2012, 11:43 AM
  4. Counting blank and filled cells within a range.
    By greg7468 in forum Excel General
    Replies: 3
    Last Post: 06-28-2005, 06:05 PM
  5. [SOLVED] How do I only calculate cells which have a filled color format?
    By Phillip Bruce in forum Excel General
    Replies: 1
    Last Post: 06-23-2005, 04:05 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