Totals skipping columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 th
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 wil
allow the appearance of totals for only the columns with entries.

HTH
 
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
 
Mike,

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

Bryan
 
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]
 
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
 
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
 
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 said:
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 said:
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?action=getinfo&userid=32690
View this thread: http://www.excelforum.com/showthread.php?threadid=531714
Click to expand...
Click to expand...
 
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, 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
 
Thanks for the response Mike, and John's spotting of the subtotal and
conditionally testing that should make life easier for you.

--
 
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,
 
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
 
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.]
 
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.

--
 
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 said:
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.]
Click to expand...
 
Back
Top