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
criteria.

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.

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:

=sumif(b:b,""&"asdf",c:c)

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

=sumif(b:b,""&"",c:c)
or more simply
=sumif(b:b,"",c:c)

It's kind of like adding 0 to a value:
3+0
and
3
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:
=sum(c2:c65536)
(or whatever the number of rows you have in the version of excel you're using)



SteveDJ wrote:

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

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

=SUMIF(B:B,"",C:C)

--
Best Regards,

Luke M
"SteveDJ" wrote in message
...
I know, this is kinda cheesy, but seems to work - I'm just missing the
right
criteria.

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.

I'd keep it simple, just use In A1: =SUM(A2:A65536)
--
Max
Singapore
---
"SteveDJ" wrote:
I know, this is kinda cheesy, but seems to work - I'm just missing the right
criteria.

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.