Hi,
Part of my spreadsheet has twelve columns, one for each month. To the right
of this, is a column for the yearly total. I am trying to use a formula that
will total up the sums across a row for each month. The problem is that if
there is nothing entered into the monthly columns, then i don't want the
total column to show zeros as this looks messy and may confuse some of the
clerks who will be using this spreadsheet and have minimal excel training. I
had posed this question here before and been told to use the
"=IF(COUNT(AT17,AU17,AV17,AW17,AX17,AY17,AZ17,BA17 ,BB17,BC17,BD17,BE17)=1,SUM(AT17:BE17),"")"
formula. This seemed to work well....until i entered values in for more than
one of the months. When i do that, the total column just stays blank instead
of adding up the monthly totals. Please help!

Thanks. Shannan.

If the count is greater than or equal to 1 then sum...

=IF(COUNT(AT17,AU17,AV17,AW17,AX17,AY17,AZ17,BA17, BB17,BC17,BD17,BE17)=1,SUM(AT17:BE17),"")
--
HTH...

Jim Thomlinson


"Shannan" wrote:

Hi,
Part of my spreadsheet has twelve columns, one for each month. To the right
of this, is a column for the yearly total. I am trying to use a formula that
will total up the sums across a row for each month. The problem is that if
there is nothing entered into the monthly columns, then i don't want the
total column to show zeros as this looks messy and may confuse some of the
clerks who will be using this spreadsheet and have minimal excel training. I
had posed this question here before and been told to use the
"=IF(COUNT(AT17,AU17,AV17,AW17,AX17,AY17,AZ17,BA17 ,BB17,BC17,BD17,BE17)=1,SUM(AT17:BE17),"")"
formula. This seemed to work well....until i entered values in for more than
one of the months. When i do that, the total column just stays blank instead
of adding up the monthly totals. Please help!

Thanks. Shannan.

Try this instead
=IF(COUNT(AT17,AU17,AV17,AW17,AX17,AY17,AZ17,BA17, BB17,BC17,BD17,BE171,SUM(AT17:BE17),"")

or

=IF(SUM(AT17:BE17)0,sum(AT17:BE17),"")
--
If this helps, please remember to click yes.


"Shannan" wrote:

Hi,
Part of my spreadsheet has twelve columns, one for each month. To the right
of this, is a column for the yearly total. I am trying to use a formula that
will total up the sums across a row for each month. The problem is that if
there is nothing entered into the monthly columns, then i don't want the
total column to show zeros as this looks messy and may confuse some of the
clerks who will be using this spreadsheet and have minimal excel training. I
had posed this question here before and been told to use the
"=IF(COUNT(AT17,AU17,AV17,AW17,AX17,AY17,AZ17,BA17 ,BB17,BC17,BD17,BE17)=1,SUM(AT17:BE17),"")"
formula. This seemed to work well....until i entered values in for more than
one of the months. When i do that, the total column just stays blank instead
of adding up the monthly totals. Please help!

Thanks. Shannan.

=IF(COUNT(AT17:BE17),SUM(AT17:BE17),"")
or
=IF(SUM(AT17:BE17)0,SUM(AT17:BE17),"")

If this post helps click Yes
---------------
Jacob Skaria


"Shannan" wrote:

Hi,
Part of my spreadsheet has twelve columns, one for each month. To the right
of this, is a column for the yearly total. I am trying to use a formula that
will total up the sums across a row for each month. The problem is that if
there is nothing entered into the monthly columns, then i don't want the
total column to show zeros as this looks messy and may confuse some of the
clerks who will be using this spreadsheet and have minimal excel training. I
had posed this question here before and been told to use the
"=IF(COUNT(AT17,AU17,AV17,AW17,AX17,AY17,AZ17,BA17 ,BB17,BC17,BD17,BE17)=1,SUM(AT17:BE17),"")"
formula. This seemed to work well....until i entered values in for more than
one of the months. When i do that, the total column just stays blank instead
of adding up the monthly totals. Please help!

Thanks. Shannan.

If you want to retain that formula, then change it to this:

=IF(COUNT(AT17,AU17,AV17,AW17,AX17,AY17,AZ17,BA17, BB17,BC17,BD17,BE17)
0,S*UM(AT17:BE17),"")

or you can simplify it to:

=IF(COUNT(AT17:BE17)0,S*UM(AT17:BE17),"")

Alternatively, you could just have:

=SUM(AT17:BE17)

and apply conditional formatting to the cell such that if the cell
content is zero then use white as the foreground colour (so that 0
looks blank on a white background).

Hope this helps.

Pete

On Sep 21, 5:31*pm, Shannan wrote:
Hi,
Part of my spreadsheet has twelve columns, one for each month. To the right
of this, is a column for the yearly total. I am trying to use a formula that
will total up the sums across a row for each month. The problem is that if
there is nothing entered into the monthly columns, then i don't want the
total column to show zeros as this looks messy and may confuse some of the
clerks who will be using this spreadsheet and have minimal excel training.. I
had posed this question here before and been told to use the
"=IF(COUNT(AT17,AU17,AV17,AW17,AX17,AY17,AZ17,BA17 ,BB17,BC17,BD17,BE17)=1,S*UM(AT17:BE17),"")"
formula. This seemed to work well....until i entered values in for more than
one of the months. When i do that, the total column just stays blank instead
of adding up the monthly totals. Please help!

Thanks. Shannan.