Summing entire column

[Drew]

I have a 2 worksheet workbook that is being used as a Credit Card log. The
user enters all the transactions into a worksheet named, CreditCardLog. The
CreditCardLog worksheet takes 10 values, PONum, PODate, Vendor, Description,
Amount, CCCObjectCode, C, CO, CR, PCO. The totals are then displayed on the
first worksheet, Totals. This adds up C, CO, CR, PCO columns for totals.
These sums are done like this,

=SUM(CreditCardLog!H2:CreditCardLog!H65536)

The issue that I am having is that if a user inserts a new line or pastes
some content into the CreditCardLog worksheet, then the sums on Totals are
#REF.

How can I sum the entire column while still allowing the users to
insert/delete rows, cut/copy/paste rows?

Thanks,
Drew Laing

 

[Anne Troy]

Have you tried:
=sum(CreditCardLog!$H:$H)
************
Anne Troy
www.OfficeArticles.com

 

[Drew]

I have tried that, and it did not work. I am guessing it didn't work
because there is a heading in the 1st row.

Thanks,
Drew

 

[Anne Troy]

Works for me just fine, even with a header, Drew. What is "not working"?
************
Anne Troy
www.OfficeArticles.com

 

[Zack Barresse]

Hello Drew,

As it is quite a valid question, the problem is the action itself.
Inserting/deleting rows is not a good idea. I personally lothe the feature.
If you can find a way to work around having to do that, it would be much
easier. If you do not want to do that, maybe try these steps ...

1) Hit Ctrl + F3, as name enter "BigNum" and RefersTo as
"=9.99999999999E+307", click Add.
2) Do not close the dialog box, as name enter "CCTotals", RefersTo as
"=INDEX(CreditCardLog!$H:$H,MATCH(BigNum,CreditCardLog!$H:$H))", click Add.
3) Use your formula as "=SUM(H2:CCTotals)

HTH

 

[Drew]

Anne,

Maybe it is working for you because you have it all on one worksheet... If I
try to enter the following,

=SUM(CreditCardLog!G:CreditCardLog!G)

I get,

The formula entered contains an error... blah, blah, blah

So how are you getting it to work?

Drew

 

[Anne Troy]

In another workbook, this works:
=SUM([Book1]Sheet1!$B:$B)
And don't put the sheet name twice.
************
Anne Troy
www.OfficeArticles.com

 

[Drew]

Thank you so much for your answer... I saw this solution while researching,
but couldn't get it working.

Thanks!
Drew

In another workbook, this works:
=SUM([Book1]Sheet1!$B:$B)
And don't put the sheet name twice.
************
Anne Troy
www.OfficeArticles.com

Anne,

Maybe it is working for you because you have it all on one worksheet...
If I try to enter the following,

=SUM(CreditCardLog!G:CreditCardLog!G)

I get,

The formula entered contains an error... blah, blah, blah

So how are you getting it to work?

Drew

 

[Anne Troy]

You're welcome, Drew.
************
Anne Troy
www.OfficeArticles.com

Thank you so much for your answer... I saw this solution while
researching, but couldn't get it working.

Thanks!
Drew

In another workbook, this works:
=SUM([Book1]Sheet1!$B:$B)
And don't put the sheet name twice.
************
Anne Troy
www.OfficeArticles.com

Anne,

Maybe it is working for you because you have it all on one worksheet...
If I try to enter the following,

=SUM(CreditCardLog!G:CreditCardLog!G)

I get,

The formula entered contains an error... blah, blah, blah

So how are you getting it to work?

Drew
Works for me just fine, even with a header, Drew. What is "not
working"?
************
Anne Troy
www.OfficeArticles.com

I have tried that, and it did not work. I am guessing it didn't work
because there is a heading in the 1st row.

Thanks,
Drew


Have you tried:
=sum(CreditCardLog!$H:$H)
************
Anne Troy
www.OfficeArticles.com

I have a 2 worksheet workbook that is being used as a Credit Card
log. The user enters all the transactions into a worksheet named,
CreditCardLog. The CreditCardLog worksheet takes 10 values, PONum,
PODate, Vendor, Description, Amount, CCCObjectCode, C, CO, CR, PCO.
The totals are then displayed on the first worksheet, Totals. This
adds up C, CO, CR, PCO columns for totals. These sums are done like
this,

=SUM(CreditCardLog!H2:CreditCardLog!H65536)

The issue that I am having is that if a user inserts a new line or
pastes some content into the CreditCardLog worksheet, then the sums
on Totals are #REF.

How can I sum the entire column while still allowing the users to
insert/delete rows, cut/copy/paste rows?

Thanks,
Drew Laing

 

[Haukwa]

Drew,

Similar to Zack's approach, you could use a Dynamic Range Name as
follows:

CCTotals
=OFFSET(CreditCardLog!$H$1,1,0,MATCH(BigNum,CreditCardLog!$H:$H)-1,1)

The OFFSET function creates a range that starts at H2 (offset one row
from H1, to allow for a column label?) and is
"MATCH(BigNum,CreditCardLog!$H:$H)" rows tall minus one row (for the
title) and 1 column wide.

You can then add up the data by using "=SUM(CCTotals)". Any rows that
are added or deleted will be dynamically reflected in the Range
definition. The two actions that could cause problems would be the
addition of a row before the column label, or the deletion of the
column label.

I hope this helps.

Gerry

 

[Zack Barresse]

My solution is dynamic. Just another form than what you have. I do like
the Offset version, but it will fail if blanks are present and a
non-contiguous range is used.

 

[Peo Sjoblom]

1. to get that last formula to work you need to refer to the CreditCardLog
sheet (assuming that the OP wants to sum from another sheet)

=SUM(CreditCardLog!H2:CCTotals)


however if you insert a row at the top and put a new value in H2 it will
fail, you would need something like

=SUM(INDIRECT("CreditCardLog!H2"):CCTotals)

--

Regards,

Peo Sjoblom

 

[Zack Barresse]

Good points Peo, should've mentioned that. Thanks.

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)
To email, remove the NO SPAM. Please keep correspondence to the board, as
to benefit others.

 

[Haukwa]

Zack,

I did not mean to imply that your solution was not dynamic. I liked
your use of MATCH because it avoids the problem associated with blanks
(or so I thought). Where do you see a problem with blanks using the
OFFSET/MATCH combo?

Thanks.

Gerry

 

[Peo Sjoblom]

Only problem with both solutions is that they don't work with rows inserted
at the top, you need to either adapt the OFFSET part with a row that will
offset the OFFSET function or make the reference a text string that won't
change like this, else $H$1 will change to H4 (if 3 rows are inserted) and
then if you fill in H2 it won't be included

=OFFSET(INDIRECT("CreditCardLog!$H$1"),1,0,MATCH(BigNum,CreditCardLog!$H:$H)
-1,1)

 

[Zack Barresse]

Haukwa, I'm very sorry. I have been in a rush in the morning and speedily
read through your response. I had thought that I read COUNTA, but must have
been somewhere else. I apologize for any confusion, my sincerest regrets.

 

[Haukwa]

Peo,

Agreed. I like your use of the INDIRECT function to force a static
cell reference.

Gerry