+ Reply to Thread
Results 1 to 14 of 14

Counting cells only if adjacent cell is filled

  1. #1
    Registered User
    Join Date
    05-28-2020
    Location
    California, USA
    MS-Off Ver
    2019
    Posts
    4

    Counting cells only if adjacent cell is filled

    Hello,

    I am a novice Excel user. I am trying to find a formula that will allow me to calculate the sum of a range of cells only if the cell adjacent to each cell in the range is empty. If the cell adjacent to the criterion cell is not empty, I would like Excel to use that cell for the sum instead. For example:


    Excel Example.png


    I have entered in my projected expenses for the month. In the actual expenses column I have entered in the data as those expenses have occurred. Sometimes the projected expense is higher than the actual expense. Sometimes the actual expense is $0. The blank cells in the actual expense column are for expenses that have not occurred yet.

    I would like to calculate the sum of my projected expenses that take into account the actual expense amounts that have already occurred. Right now I am currently using a Pivot table which calculates the total sum of all cells in the projected expense amount range which comes out to be $6278.27. But what I want to see is $3424.27 which uses the actual expense amounts when they are present instead of using only the projected expense amounts.

    I apologize if this is confusing! I'm trying to figure out a solution and am having a hard time figuring out how to articulate the issue which makes it even more challenging! Any guidance is appreciated. Thank you.

  2. #2
    Valued Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    674

    Re: Counting cells only if adjacent cell is filled

    First, it's always best to attach a sample file (please see the yellow banner at the top of this page). That being said, would this formula work for you?

    =SUM(B3:B10)+SUMIFS(A3:A10,B3:B10,"")
    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  3. #3
    Registered User
    Join Date
    05-28-2020
    Location
    California, USA
    MS-Off Ver
    2019
    Posts
    4

    Re: Counting cells only if adjacent cell is filled

    Hi Gregb11,

    Thanks for your reply. Attached is the workbook. As for the formula you recommended, that would not work for me. I'm not quite sure what number that formula is calculating but it is neither the total projected expense, total actual expense, nor the projected expense with the conditions I am looking for. Thank you for sharing though!
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    674

    Re: Counting cells only if adjacent cell is filled

    Why isn't it right? I thought you wanted to add all Actual Expenses if they occurred, and they haven't occurred (meaning they're blank), then use the projected expense. In your example, that would total $5528.27, which is what the formula returns.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,461

    Re: Counting cells only if adjacent cell is filled

    Just guess:
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    05-28-2020
    Location
    California, USA
    MS-Off Ver
    2019
    Posts
    4

    Re: Counting cells only if adjacent cell is filled

    Gregb11: I'm looking for the reverse, I guess, of what you're suggesting. The number I want to calculate is total projected expense, then, if the actual expense is different than the projected expense (any number including $0), use that number in the calculation instead. If the actual expense is blank, then continue using the projected expense amount in the calculation. In the example I provided, I'm trying to get to the number $3424.27 for my total projected expense. Hope that helps!

  7. #7
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,828

    Re: Counting cells only if adjacent cell is filled

    Hi you can simply add them and you get the required amount but still, your logic isn't clear

    Punnam

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,461

    Re: Counting cells only if adjacent cell is filled

    Quote Originally Posted by rwebb_5 View Post
    Gregb11: I'm looking for the reverse, I guess, of what you're suggesting. The number I want to calculate is total projected expense, then, if the actual expense is different than the projected expense (any number including $0), use that number in the calculation instead. If the actual expense is blank, then continue using the projected expense amount in the calculation. In the example I provided, I'm trying to get to the number $3424.27 for my total projected expense. Hope that helps!
    You are expecting total ACTUAL (=$3424.27 ), and ignore PROJECT value where ACTUAL is emty (=2054+50=1104)
    Follow your words, the results should be: 3424.27+1104=5,528.27 is what Gregb11 suggest.

  9. #9
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,828

    Re: Counting cells only if adjacent cell is filled

    HI bebo021999,

    My understanding is
    Total the amount in column "B"
    Criteria-1 Sum values for which in column "B"<>""
    Criteria-2 Sum data in Column B based on Vales in Column "A" for which having values corresponding to Column B <> ""

    Punnam
    Attached Files Attached Files
    Last edited by Punnam; 05-29-2020 at 12:41 AM.

  10. #10
    Registered User
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003
    Posts
    73

    Re: Counting cells only if adjacent cell is filled

    I think the problem is that sum of his cells are 0 and some are null
    I don't have SUMIFS (of course)

    But this will give an answer of $5778.67, which is what I think he wants.
    Please Login or Register  to view this content.
    Having said that, what he actually wrote is a difference of $250 to what I have put (as suggested above).
    Last edited by Croweater; 05-29-2020 at 01:16 AM.

  11. #11
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,828

    Re: Counting cells only if adjacent cell is filled

    Hi Croweater,

    In Post #6 he confirmed "'i am trying to get to the number $3424.27 for my total projected expense"

    Punnam

  12. #12
    Registered User
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003
    Posts
    73

    Re: Counting cells only if adjacent cell is filled

    Hi Punnam,

    You are right..I didn't see that bit. But $3424.27 is just the SUM(B3:B10) ! Mass confusion!

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,641

    Re: Counting cells only if adjacent cell is filled

    Yes - the logic is shot.

    This does exactly what the OP describes:

    =SUMIF($B$3:$B$10,"",$A$3:$A$10)+SUM($B$3:$B$10)

    But this gives the answer he says he wants:

    =SUM($B$3:$B$10)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  14. #14
    Registered User
    Join Date
    05-28-2020
    Location
    California, USA
    MS-Off Ver
    2019
    Posts
    4

    Re: Counting cells only if adjacent cell is filled

    Hi everyone,

    My apologies for falling off this thread. I just came back to this problem and inputted the formula that Gregb11 suggested. I must have not inputted it correctly the first time which resulted in skewed results but I just inputted the formula again and it worked! Again, sorry Gregb11 for my error. Thank you for helping me with this solution. It's greatly appreciated. Thanks to everyone who tried to navigate through the confusion I caused. Take care.

+ 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. Counting the sum of a cell based on an adjacent cells value
    By QS-Mikey in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-21-2020, 11:26 AM
  2. Replies: 6
    Last Post: 11-11-2016, 09:07 PM
  3. Replies: 3
    Last Post: 10-20-2016, 02:06 PM
  4. Sum adjacent cells based onion filled
    By jser63 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-04-2016, 08:16 AM
  5. Replies: 12
    Last Post: 07-10-2012, 05:04 PM
  6. Formula Added Only When Adjacent Cells Filled
    By formula in forum Excel General
    Replies: 6
    Last Post: 03-18-2010, 12:29 AM
  7. Replies: 2
    Last Post: 03-22-2006, 01:25 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