# Counting cells only if adjacent cell is filled

1. ## 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.  Register To Reply

2. ## 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,"")  Register To Reply

3. ## 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!  Register To Reply

4. ## 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.  Register To Reply

5. ## Re: Counting cells only if adjacent cell is filled

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

6. ## 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!  Register To Reply

7. ## 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  Register To Reply

8. ## Re: Counting cells only if adjacent cell is filled Originally Posted by rwebb_5 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.  Register To Reply

9. ## 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  Register To Reply

10. ## 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).  Register To Reply

11. ## 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  Register To Reply

12. ## 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!  Register To Reply

13. ## 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)  Register To Reply

14. ## 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.  Register To Reply