+ Reply to Thread
Results 1 to 12 of 12

[SOLVED] Count number of empty cells and sum up too the first non-empty cell.

  1. #1
    Registered User
    Join Date
    06-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    11

    [SOLVED] Count number of empty cells and sum up too the first non-empty cell.

    Hi, I've been doing some detailed diet planning and have put together a spreadsheet to calculate various micro/macro nutrients and have attached it.

    So far i have a sheet containing data on the contents of various ingredients and am constructing a sheet allowing you to create meals from the ingredients which will then give you a total for the meal in the row at the bottom of your list of ingredients.
    In the sheet "Meals" I have it setup to lookup the data from the ingredients tab and fill in based on the drop down on the left.

    The aim is that if you select total from the dropdown in column E of meals it will then total everything in the rows above up too and including the row including the name of the meal. For example the "tomato egg chorizo" meal once you select total underneath the egg ingredient would automatically calculate the total for all nutrients in the meal in the same row as the total in B5.

    I was thinking I'd be able to change the True portion of the if statement in E5 allowing me to copy it to the rest of the sheet but i can't work out how to get match or countblank too work in this manner.

    I hope it is clear what I am trying to achieve,

    Thankyou!
    Attached Files Attached Files
    Last edited by CraftyGamer; 11-28-2017 at 11:01 AM.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Count number of empty cells and sum up too the first non-empty cell.

    There seems to be a problem with the workbook that you have shared. I am getting a warning message when attempting to open it.

    If you can fix this issue, we will happily take a look.

  3. #3
    Registered User
    Join Date
    06-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Count number of empty cells and sum up too the first non-empty cell.

    It was an export from google sheets - their export doesnt seem to work properly for me either i probably shoudlve opened it myself before posting, sorry! Heres a direct editable link to a copy of the sheet ill try and get the file working if you'd rather have that.

    https://docs.google.com/spreadsheets...it?usp=sharing

  4. #4
    Registered User
    Join Date
    06-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Count number of empty cells and sum up too the first non-empty cell.

    Ive updated the OP with the fixed spreadsheet, theres supposed to be a drop down on the ingredient column in the meals tabt but the export has removed it, for the purposes of this i dont think it will make a difference.

    Thanks again.
    Last edited by CraftyGamer; 11-28-2017 at 10:02 AM.

  5. #5
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Count number of empty cells and sum up too the first non-empty cell.

    use COUNTBLANK or
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    06-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Count number of empty cells and sum up too the first non-empty cell.

    Sorry, the aim is too calculate the total for the carbohydrate column for example the expected result for the tomato egg chorizo meal would be 0.95+2+0.4=3.35 which would appear in E5 in the same row as total. The amounts column is just used to calculate the nutrients found in that specific ingredient. Id then want to copy this formula too the rest of the sheet.

  7. #7
    Registered User
    Join Date
    06-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Count number of empty cells and sum up too the first non-empty cell.

    The formula found in E5 below, the IF(A6="Total",1, portion is the only pertinent part as the rest works fine. I just want to replace the 1 with some a method that would work as above and be simple to copy allowing me to add things to the sheet over time.

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    06-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Count number of empty cells and sum up too the first non-empty cell.

    I managed it using

    Please Login or Register  to view this content.

  9. #9
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: [SOLVED] Count number of empty cells and sum up too the first non-empty cell.

    This works as well in B2 and drag:
    Please Login or Register  to view this content.
    If it took care of your problem, please mark the tread as Solved. Thanks

  10. #10
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: [SOLVED] Count number of empty cells and sum up too the first non-empty cell.

    Here's something else for you to consider (I implemented everything through row 100):

    Data Validation
    Highlight B2:B100 > Data > Data Validation > Allow: List > Source: =Ingredients!$A$2:$A$30 > OK

    Conditional Formatting
    Highlight A2:AI100 > Conditional Formatting > New Rule > Use a formula
    =$B2="Total"
    Format: Grey fill with bold font

    Formulas
    D2 =IFERROR(E2*4+F2*9+G2*4,"")
    E2 =IFERROR(IF($B2="Total",SUM(E1:E$2)-SUMIF($B1:$B$2,"Total",E1:E$2)*2,VLOOKUP($B2,Ingredients!$A$1:$AG$29,COLUMN(A$1)+2,FALSE)*$C2),"")

    Drag the formula in E2 through AI2 and then highlight D2:AI2 and drag down through row 100.

    See attached workbook for clarification.
    Attached Files Attached Files
    Last edited by 63falcondude; 11-28-2017 at 12:06 PM. Reason: Typo

  11. #11
    Registered User
    Join Date
    06-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: [SOLVED] Count number of empty cells and sum up too the first non-empty cell.

    Thanks a lot for the help. Great method much easier to copy than mine!

  12. #12
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: [SOLVED] Count number of empty cells and sum up too the first non-empty cell.

    You're welcome. Happy to help.

    It might also run better without the volatile INDIRECT function.

+ 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] Is it possible to count only the number of empty cells when using autofilter?
    By chief_abound in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-12-2017, 09:59 PM
  2. [SOLVED] Loop That Will Count Number Of Comments In a Column Between Empty Cells
    By M7A0S8T8E5R in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-15-2016, 04:32 PM
  3. Replies: 5
    Last Post: 02-01-2016, 05:57 PM
  4. Replies: 10
    Last Post: 09-26-2015, 08:26 PM
  5. Replies: 2
    Last Post: 07-25-2013, 01:01 PM
  6. Count the number of cells in a range that are not empty
    By efernandes67 in forum Excel General
    Replies: 5
    Last Post: 07-23-2010, 05:41 AM

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