How to SUM entire column, but only when adjacent cell is not empty



I know, this is kinda cheesy, but seems to work - I'm just missing the right

I have a column of data that continues to grow, so I need to sum the entire
thing just using C:C. However, the catch is, I want this sum total to be in
the same column, but at the top. This of course causes a circular ref error.

In column B is data that goes with the neighboring cell in column C. So, I
want to use =SUMIF(B:B, <not-sure-of-criteria> ,C:C), but cannot find the
correct criteria to say that if B contains anything, then add the C data.

To avoid the circular reference, I just make sure that the B cell next to my
sum-formula cell in C is blank. :)

I know this is probably easy, but I'm having troubles searching for the
right thing, so hence not finding my answer.



Dave Peterson

If you wanted to use =sumif() to sum the values in column C if column B did not
contain ASDF, you could use a formula like:


So I could change that "asdf" to "" to check for non-empty:

or more simply

It's kind of like adding 0 to a value:
represent the same number.

But I think you're going to have the same circular reference error.

I'd just use this formula in C1:
(or whatever the number of rows you have in the version of excel you're using)


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question