+ Reply to Thread
Results 1 to 5 of 5

Sum If Blank Cells

  1. #1
    Registered User
    Join Date
    08-10-2017
    Location
    Berkshire, England
    MS-Off Ver
    Microsoft Office 2010
    Posts
    16

    Sum If Blank Cells

    Hi,

    I need a formula that will sum up the total usage for all users of each report. For example, i need to formula in Column F to sum up Report 1's usage (Which would be 25 as there is only 1 user), and then sum up Report 2's usage (Which would be 65) etc. But what i also need is for numbers to only appear where the report name is. For example, for Report 2 i'd need the 65 to appear in D3, and then the rest of the cells be blank until D18 for Report 3's total usage, and then blank up until Report 4 etc.

    img1.PNG

    I've attached the example file.

    I hope that all make sense, but let me know if not.

    Many thanks,
    swood
    Attached Files Attached Files
    Last edited by swood91; 02-19-2021 at 02:28 PM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Sum If Blank Cells

    Yep. In D2, copied down:

    =IF(A2<>"",SUMPRODUCT((LOOKUP(ROW($A$2:$A$48),ROW($A$2:$A$48)/($A$2:$A$48>0),$A$2:$A$48)=A2)*$C$2:$C$48),"")
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    08-10-2017
    Location
    Berkshire, England
    MS-Off Ver
    Microsoft Office 2010
    Posts
    16

    Re: Sum If Blank Cells

    Quote Originally Posted by Glenn Kennedy View Post
    Yep. In D2, copied down:

    =IF(A2<>"",SUMPRODUCT((LOOKUP(ROW($A$2:$A$48),ROW($A$2:$A$48)/($A$2:$A$48>0),$A$2:$A$48)=A2)*$C$2:$C$48),"")
    That's exactly what i needed. Thanks so much for the quick reply

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Sum If Blank Cells

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

  5. #5
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Sum If Blank Cells

    Another way:

    =IF(A2="","",SUM(INDEX($C$1:C2,MATCH(1,(--($A$1:A2<>"")))):INDEX($C$1:$C$48,IFERROR(AGGREGATE(15,6,(ROW(A3:$A$48)/(A3:$A$48<>"")),1)-1,MATCH(1,(--($B$1:$B$48<>"")))))))
    Attached Files Attached Files

+ 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. Removing Blank Cells in the Pivot Table. (Blank) - Out of Range Cells
    By satishmen in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-09-2019, 06:30 AM
  2. Replies: 12
    Last Post: 05-29-2018, 05:11 PM
  3. Replies: 3
    Last Post: 07-24-2017, 01:41 PM
  4. Count blank cells in a range and contiguous blank cells also as single cells! Tricky One!
    By SebastianColombia in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-31-2015, 02:32 PM
  5. [SOLVED] Macro to fill blank cells in column A based on non-blank cells
    By ktalamantez in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-28-2014, 02:47 PM
  6. Replies: 5
    Last Post: 08-26-2013, 06:26 PM
  7. Advanced filter with blank cells / Dynamic named range with blank cells
    By Jason_2112 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-15-2010, 12:06 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