I have data that is entered into each column by different people. Each
column is totaled. Below the column total is a running total with each
column added to the last. The formula used for that column is:
=IF(AND(E18,F17),E18+F17,""). What I need is that if a column or even two is
not filled in I need the total, basically skipping the empty columns. Is
this possible?
Thanks for any help offered!!!

Mike Rogers

You could use Conditional Format on the column totals, such that if the
count of this column items = zero then the text is white-on-white (ie,
hidden)

This will accomodate the entries in the column totaling zero, and will
allow the appearance of totals for only the columns with entries.

HTH


Mike Rogers Wrote:
I have data that is entered into each column by different people. Each
column is totaled. Below the column total is a running total with
each
column added to the last. The formula used for that column is:
=IF(AND(E18,F17),E18+F17,""). What I need is that if a column or even
two is
not filled in I need the total, basically skipping the empty columns.
Is
this possible?
Thanks for any help offered!!!

Mike Rogers


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=531714

Thanks for the reply Bryan but that does not help with the running total that
is carried across the bottom of all the columns. Right now if I skip a
column the rest of the columns have the ole "#VALUE!" error. I am trying to
keep a running total across the columns even if I skip one (or two).

Mike Rogers

"Bryan Hessey" wrote:


You could use Conditional Format on the column totals, such that if the
count of this column items = zero then the text is white-on-white (ie,
hidden)

This will accomodate the entries in the column totaling zero, and will
allow the appearance of totals for only the columns with entries.

HTH


Mike Rogers Wrote:
I have data that is entered into each column by different people. Each
column is totaled. Below the column total is a running total with
each
column added to the last. The formula used for that column is:
=IF(AND(E18,F17),E18+F17,""). What I need is that if a column or even
two is
not filled in I need the total, basically skipping the empty columns.
Is
this possible?
Thanks for any help offered!!!

Mike Rogers


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=531714

Mike,

Keep the totals going across the blank columns and, if possible, use
=Sum(B1:B98)+A99 etc, which ignores blanks.

Bryan


Mike Rogers Wrote:
Thanks for the reply Bryan but that does not help with the running total
that
is carried across the bottom of all the columns. Right now if I skip
a
column the rest of the columns have the ole "#VALUE!" error. I am
trying to
keep a running total across the columns even if I skip one (or two).

Mike Rogers

"Bryan Hessey" wrote:


You could use Conditional Format on the column totals, such that if
the
count of this column items = zero then the text is white-on-white
(ie,
hidden)

This will accomodate the entries in the column totaling zero, and
will
allow the appearance of totals for only the columns with entries.

HTH


Mike Rogers Wrote:
I have data that is entered into each column by different people.
Each
column is totaled. Below the column total is a running total with
each
column added to the last. The formula used for that column is:
=IF(AND(E18,F17),E18+F17,""). What I need is that if a column or
even
two is
not filled in I need the total, basically skipping the empty
columns.
Is
this possible?
Thanks for any help offered!!!

Mike Rogers


--
Bryan Hessey

------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=531714




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=531714

Hi Mike,

I assume your problem is that when you hit a blank column, you get an
error in your running total. If so, try changing your formula to
=IF(AND(E18,F17),E18+F17,0). Zeros can be made invisible by selecting
tools-options-view and unchecking the zero values box.
[If you also additionally want to hide the totals in empty columns, you
can use conditional formatting to achieve this, but I don't read this as
your requirement]


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531714

Bryan,
Thanks for the post back, that works almost, but now all the way across the
row is the same total as the last column with data because it ignors blanks,
now if I go to an "IF(AND)" function to only have totals to the last column
with data it also ignors the blank columns. I think I am going in circles.
And it sounds like I can't have it both ways. Any other ideas?

Mike Rogers

"Bryan Hessey" wrote:


Mike,

Keep the totals going across the blank columns and, if possible, use
=Sum(B1:B98)+A99 etc, which ignores blanks.

Bryan


Mike Rogers Wrote:
Thanks for the reply Bryan but that does not help with the running total
that
is carried across the bottom of all the columns. Right now if I skip
a
column the rest of the columns have the ole "#VALUE!" error. I am
trying to
keep a running total across the columns even if I skip one (or two).

Mike Rogers

"Bryan Hessey" wrote:


You could use Conditional Format on the column totals, such that if
the
count of this column items = zero then the text is white-on-white
(ie,
hidden)

This will accomodate the entries in the column totaling zero, and
will
allow the appearance of totals for only the columns with entries.

HTH


Mike Rogers Wrote:
I have data that is entered into each column by different people.
Each
column is totaled. Below the column total is a running total with
each
column added to the last. The formula used for that column is:
=IF(AND(E18,F17),E18+F17,""). What I need is that if a column or
even
two is
not filled in I need the total, basically skipping the empty
columns.
Is
this possible?
Thanks for any help offered!!!

Mike Rogers


--
Bryan Hessey

------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=531714




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=531714

John,

Thanks for the post back, that still does not skip empty columns. I am
beginning to think there is not small solution for this. Any other ideas
would be appreciated!!

Is there a way of doing this with a nested "IF" statement that would look
back and see if it is a zero or not?

Mike Rogers

"John James" wrote:


Hi Mike,

I assume your problem is that when you hit a blank column, you get an
error in your running total. If so, try changing your formula to
=IF(AND(E18,F17),E18+F17,0). Zeros can be made invisible by selecting
tools-options-view and unchecking the zero values box.
[If you also additionally want to hide the totals in empty columns, you
can use conditional formatting to achieve this, but I don't read this as
your requirement]


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531714

Mike,

If you use =Sum to do your totals you will not get the #Value errors
for blanks.

Then put the totals across all columns, and hide the columns that you
do not want to see, ie, where count of items = zero.

If your column total is in row 17, then in B17 somthing like
=Sum(B1:B16), and in B18 =Sum(A18+B17).

Then use the conditional format as shown to hide unused columns, does
this not provide the result you seek?

--

Mike Rogers Wrote:
John,

Thanks for the post back, that still does not skip empty columns. I
am
beginning to think there is not small solution for this. Any other
ideas
would be appreciated!!

Is there a way of doing this with a nested "IF" statement that would
look
back and see if it is a zero or not?

Mike Rogers

"John James" wrote:


Hi Mike,

I assume your problem is that when you hit a blank column, you get
an
error in your running total. If so, try changing your formula to
=IF(AND(E18,F17),E18+F17,0). Zeros can be made invisible by
selecting
tools-options-view and unchecking the zero values box.
[If you also additionally want to hide the totals in empty columns,
you
can use conditional formatting to achieve this, but I don't read this
as
your requirement]


--
John James

------------------------------------------------------------------------
John James's Profile:
http://www.excelforum.com/member.php...o&userid=32690
View this thread:
http://www.excelforum.com/showthread...hreadid=531714




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=531714

Mike,

OK. Now I understand what you mean (I think). Try this. In F18,
enter:
=IF(F17="","",SUM($A17:F17))

Then copy across the row.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531714

John, Bryan,
Guys you did great! I took a little of what you where both saying and made
it work. The Sum formula worked and does ignor blanks, but it also carries
the total of the last column totaled all the way to the right edge of my work
sheet, did not like that! So I referenced the total cell with conditional
formating and said if this cell is <.25 ((a reference number that will always
be less than anything in the column)) then formate the continious total with
white font. Picked it up with the format painter and finished the row out.
Then I grouped all 20 worksheets and made them all work right, so now thanks
to you guys I am back in the saddle again. Thanks again for the help.

Mike Rogers

"John James" wrote:


Mike,

OK. Now I understand what you mean (I think). Try this. In F18,
enter:
=IF(F17="","",SUM($A17:F17))

Then copy across the row.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531714

Thanks for the response Mike, and John's spotting of the subtotal and
conditionally testing that should make life easier for you.

--

Mike Rogers Wrote:
John, Bryan,
Guys you did great! I took a little of what you where both saying and
made
it work. The Sum formula worked and does ignor blanks, but it also
carries
the total of the last column totaled all the way to the right edge of
my work
sheet, did not like that! So I referenced the total cell with
conditional
formating and said if this cell is <.25 ((a reference number that will
always
be less than anything in the column)) then formate the continious total
with
white font. Picked it up with the format painter and finished the row
out.
Then I grouped all 20 worksheets and made them all work right, so now
thanks
to you guys I am back in the saddle again. Thanks again for the help.

Mike Rogers

"John James" wrote:


Mike,

OK. Now I understand what you mean (I think). Try this. In F18,
enter:
=IF(F17="","",SUM($A17:F17))

Then copy across the row.


--
John James

------------------------------------------------------------------------
John James's Profile:
http://www.excelforum.com/member.php...o&userid=32690
View this thread:
http://www.excelforum.com/showthread...hreadid=531714




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=531714

Hi Mike,

When I said copy across the row, I should have said only as far as you
feel is necessary. If any column is blank (including at the end), the
total shouldn't appear, so I don't understand the need for conditional
formatting.

Having said that, if you're happy, I'm happy - however you've achieved
that - and I'm glad you're back in the saddle.

Bryan and I both appreciate your feedback.

Cheers,


Mike Rogers Wrote:
John, Bryan,
Guys you did great! I took a little of what you where both saying and
made
it work. The Sum formula worked and does ignor blanks, but it also
carries
the total of the last column totaled all the way to the right edge of
my work
sheet, did not like that! So I referenced the total cell with
conditional
formating and said if this cell is <.25 ((a reference number that will
always
be less than anything in the column)) then formate the continious total
with
white font. Picked it up with the format painter and finished the row
out.
Then I grouped all 20 worksheets and made them all work right, so now
thanks
to you guys I am back in the saddle again. Thanks again for the help.

Mike Rogers

"John James" wrote:


Mike,

OK. Now I understand what you mean (I think). Try this. In F18,
enter:
=IF(F17="","",SUM($A17:F17))

Then copy across the row.


--
John James

------------------------------------------------------------------------
John James's Profile:
http://www.excelforum.com/member.php...o&userid=32690
View this thread:
http://www.excelforum.com/showthread...hreadid=531714




--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531714

John,
When I said it showed the total of the last column totaled all the way
across the worksheet I did mean that it only went to the end of my work, not
column IV. Now you have me wondering why it carries across. The formula I
have there now is
=SUM(E12,E15,D18), dragged across.to column T. In column T there is
=SUM(T12,T15,S18). It must be that I am referencing back to the previous
column to carry a total into an empty column for the next column to pick up
and carry forward. Thus a running total. Please correct me if I am wrong, cuz
I am only guessing, and am willing to learn from those who are not guessing
any more. Thanks for all your input on this.

Mike Rogers


"John James" wrote:


Hi Mike,

When I said copy across the row, I should have said only as far as you
feel is necessary. If any column is blank (including at the end), the
total shouldn't appear, so I don't understand the need for conditional
formatting.

Having said that, if you're happy, I'm happy - however you've achieved
that - and I'm glad you're back in the saddle.

Bryan and I both appreciate your feedback.

Cheers,


Mike Rogers Wrote:
John, Bryan,
Guys you did great! I took a little of what you where both saying and
made
it work. The Sum formula worked and does ignor blanks, but it also
carries
the total of the last column totaled all the way to the right edge of
my work
sheet, did not like that! So I referenced the total cell with
conditional
formating and said if this cell is <.25 ((a reference number that will
always
be less than anything in the column)) then formate the continious total
with
white font. Picked it up with the format painter and finished the row
out.
Then I grouped all 20 worksheets and made them all work right, so now
thanks
to you guys I am back in the saddle again. Thanks again for the help.

Mike Rogers

"John James" wrote:


Mike,

OK. Now I understand what you mean (I think). Try this. In F18,
enter:
=IF(F17="","",SUM($A17:F17))

Then copy across the row.


--
John James

------------------------------------------------------------------------
John James's Profile:
http://www.excelforum.com/member.php...o&userid=32690
View this thread:
http://www.excelforum.com/showthread...hreadid=531714




--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531714

Mike,

Your latest posting sums 3 figures without any conditional testing and
bears no relationship to earlier postings. If you want someone to
comment on this formula then you'll need to explain it more fully.

Let's start again on the previously advised problem.

My understanding of your problem was that in, say, column F you had
1. Column totals in row 17, which I assume had formulae like:
=if(sum(f2:f16)=0,"",sum(f2:f16))
2. Running subtotals adding across the columns in row 18 with this
formula:
=IF(AND(E18,F17),E18+F17,"")
3. You were getting error values in the row 18 formulae (which is
understandable because you were adding numbers and blanks "")
4. You didn't want anything to show in columns which had no amounts to
add, but wanted the totals to carry across to later columns
5. I've had to make assumptions about your worksheet structure which
I'm sure you can adapt. e.g. I assume your first figures are in column
B

The prime source of your confusion, I think, is that blanks "" entered
in cells through your formula are NOT zeros and are NOT the same as
blank cells and CANNOT be added in standard formulae because they will
give errors. So let's dump those blanks "" in your formulae
altogether.

Try this:
1. Select Tools-Options-View and uncheck the box labelled zero values.
This will ensure that zero values aren't visible in your worksheet (I
assume this is what you want)
2. Ensure that your formula in cell B17 evaluates to an amount:
=sum(B2:B16).
Copy this across all relevant columns in row 17.
3. Ensure that your formula in cell B18 adds the relevant figures in
row 17, ignores subtotals in row 18, and evaluates to zero, if the
figure above is 0
= IF(B17=0,0,SUM($B17:B17))
Copy this across all relevant columns in row 18.

If you do this, then you should have nothing shown in the total cells
(rows 17 and 18) in columns which are empty, but have correct totals in
other columns. There should be no need for conditional formatting.

[Note: If you can have a combination of positive and negative values
adding to zero in row 17, making a zero total in a nonj-blank column,
then first get this working and then advise that problem and we'll fix
it.]


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531714

Mike,

If you use the formula

=IF(F17="","",SUM($A17:F17))

as shown by John, this will, assuming that F17 contains a subbtotal for
the column, show the required total.

If F17 (and all row 17) does not contain a subtotal of it's column then
you will needto adjust the formula to suit.

--

Mike Rogers Wrote:
John,
When I said it showed the total of the last column totaled all the way
across the worksheet I did mean that it only went to the end of my
work, not
column IV. Now you have me wondering why it carries across. The
formula I
have there now is
=SUM(E12,E15,D18), dragged across.to column T. In column T there is
=SUM(T12,T15,S18). It must be that I am referencing back to the
previous
column to carry a total into an empty column for the next column to
pick up
and carry forward. Thus a running total. Please correct me if I am
wrong, cuz
I am only guessing, and am willing to learn from those who are not
guessing
any more. Thanks for all your input on this.

Mike Rogers


"John James" wrote:


Hi Mike,

When I said copy across the row, I should have said only as far as
you
feel is necessary. If any column is blank (including at the end),
the
total shouldn't appear, so I don't understand the need for
conditional
formatting.

Having said that, if you're happy, I'm happy - however you've
achieved
that - and I'm glad you're back in the saddle.

Bryan and I both appreciate your feedback.

Cheers,


Mike Rogers Wrote:
John, Bryan,
Guys you did great! I took a little of what you where both saying
and
made
it work. The Sum formula worked and does ignor blanks, but it also
carries
the total of the last column totaled all the way to the right edge
of
my work
sheet, did not like that! So I referenced the total cell with
conditional
formating and said if this cell is <.25 ((a reference number that
will
always
be less than anything in the column)) then formate the continious
total
with
white font. Picked it up with the format painter and finished the
row
out.
Then I grouped all 20 worksheets and made them all work right, so
now
thanks
to you guys I am back in the saddle again. Thanks again for the
help.

Mike Rogers

"John James" wrote:


Mike,

OK. Now I understand what you mean (I think). Try this. In
F18,
enter:
=IF(F17="","",SUM($A17:F17))

Then copy across the row.


--
John James


------------------------------------------------------------------------
John James's Profile:
http://www.excelforum.com/member.php...o&userid=32690
View this thread:
http://www.excelforum.com/showthread...hreadid=531714




--
John James

------------------------------------------------------------------------
John James's Profile:
http://www.excelforum.com/member.php...o&userid=32690
View this thread:
http://www.excelforum.com/showthread...hreadid=531714




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=531714

Bryan, You were right, that formula from John does work. Once I made the
adjustments for my layout. thanks

John, Thank you for your patience!!! Your assumptions where sooo close
that what you gave me works. I had to make some small reference changes but
once I understood what was going to happen, it was easy. I have to admit
that I get awful hard headed sometimes. It took me a little time to relax
and try to see what you were trying to get me to see. Makes perfect
sense....now...!!! I like your way better than what I had last night. What
I had worked but I think this is so much simpler. Thanks again.

Mike Rogers

"John James" wrote:


Mike,

Your latest posting sums 3 figures without any conditional testing an
bears no relationship to earier postings. If you want someone to
comment on this formula then you'll need to explain it more fully.

Let's start again on the previously advised problem.

My understanding of your problem was that in, say, column F you had
1. Column totals in row 17, which I assume had formulae like:
=if(sum(f2:f16)=0,"",sum(f2:f16)
2. Running subtotals adding across the columns in row 18 with this
formula:
=IF(AND(E18,F17),E18+F17,"")
3. You were getting error values in the row 18 formulae (which is
understandable because you were adding numbers and blanks "")
4. You didn't want anything to show in columns which had no amounts to
add, but wanted the totals to carry across to later columns
5. I've had to make assumptions about your worksheet structure which
I'm sure you can adapt. e.g. I assume your first figures are in column
B

The prime source of your confusion, I think, is that blanks "" entered
in cells through your formula are NOT zeros and are NOT the same as
blank cells and CANNOT be added in standard formulae because they will
give errors. So let's dump those blanks "" in your formulae
altogether.

Try this:
1. Select Tools-Options-View and uncheck the box labelled zero values.
This will ensure that zero values aren't visible in your worksheet (I
assume this is what you want)
2. Ensure that your formula in cell B17 evaluates to an amount:
=sum(B2:B16).
Copy this across all relevant columns in row 17.
3. Ensure that your formula in cell B18 adds the relevant figures in
row 17, ignores subtotals in row 18, and evaluates to zero, if the
figure above is 0
= IF(B17=0,0,SUM($B17:B17))
Copy this across all relevant columns in row 18.

If you do this, then you should have nothing shown in the total cells
(rows 17 and 18) in columns which are empty, but have correct totals in
other columns. There should be no need for conditional formatting.

[Note: If you can have a combination of positive and negative values
adding to zero in row 17, making a zero total in a nonj-blank column,
then first get this working and then advise that problem and we'll fix
it.]


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531714

Glad its working.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531714