I'm looking to total a range of cells ONLY if they have a value of 0 or more
entered in them. In plain english:

Sumif A1 is =0
Sumif A2 is =0
Sumif A3 is =0

and so on. Where every cell in the range must have a value of 0 or more
entered before the function will return a result. How do I do this?

Thanks,

PO

Hi

Try something like this:
=SUMPRODUCT((D7:G70)*(D7:G7))

AndyB

"Powlaz" wrote in message
...
I'm looking to total a range of cells ONLY if they have a value of 0 or
more
entered in them. In plain english:

Sumif A1 is =0
Sumif A2 is =0
Sumif A3 is =0

and so on. Where every cell in the range must have a value of 0 or more
entered before the function will return a result. How do I do this?

Thanks,

PO

This worked for me. First I named the range I wanted to sum SumRange
(clever, eh?) I then used this formula:

=IF(COUNTIF(SumRange,"0")=ROWS(SumRange),SUM(SumR ange),0)


"Powlaz" wrote:

I'm looking to total a range of cells ONLY if they have a value of 0 or more
entered in them. In plain english:

Sumif A1 is =0
Sumif A2 is =0
Sumif A3 is =0

and so on. Where every cell in the range must have a value of 0 or more
entered before the function will return a result. How do I do this?

Thanks,

PO

Slight change:

=IF(COUNTIF(SumRange,"=0")=ROWS(SumRange),SUM(Sum Range),0)


"Kevin Vaughn" wrote:

This worked for me. First I named the range I wanted to sum SumRange
(clever, eh?) I then used this formula:

=IF(COUNTIF(SumRange,"0")=ROWS(SumRange),SUM(SumR ange),0)


"Powlaz" wrote:

I'm looking to total a range of cells ONLY if they have a value of 0 or more
entered in them. In plain english:

Sumif A1 is =0
Sumif A2 is =0
Sumif A3 is =0

and so on. Where every cell in the range must have a value of 0 or more
entered before the function will return a result. How do I do this?

Thanks,

PO

Kevin, Andy, thanks for the quick responses. Both of your formulas worked
(as far as I can tell). I'm interested in understanding how they work if you
could offer a quick explanation.

Moreso, I need a little more help. Both the formulas returned a 0 when
summing a range of blank cells. Here's the issue, this spreadsheet is pre -
formatted for a year's worth of data. I'd like for the cells in an unused
month to remain blank until the data is entered (and subsequently
calculated).

There is currently no user added data in cells b6:b12. But cell b13 that
calculates the total of b6:b12 shows 0. I'd like it to be blank until data
is entered into b6:b12. How can I concisely do that?

I thought about using IF and OR functions combined so that each cell is
checked for a value greater than "" before it will calculate the total of the
cells but it's very long and I think inefficient.

Got anything else?

Thanks

PO
"AndyB" wrote:

Hi

Try something like this:
=SUMPRODUCT((D7:G70)*(D7:G7))

AndyB

"Powlaz" wrote in message
...
I'm looking to total a range of cells ONLY if they have a value of 0 or
more
entered in them. In plain english:

Sumif A1 is =0
Sumif A2 is =0
Sumif A3 is =0

and so on. Where every cell in the range must have a value of 0 or more
entered before the function will return a result. How do I do this?

Thanks,

PO

=IF(COUNTIF(SumRange,"=0")=ROWS(SumRange),SUM(Sum Range),"")
My sumrange was a1:a10 so rows(sumrange) returns 10. The countif will count
the number of occurences where the cells in sumrange = 0. If all of the
cells meet the criteria, this will equal 10. Therefore the true portion of
the IF statement will be returned which is SUM(Sumrange). Otherwise ""
(nothing) will be returned. This per your latest request to have "" shown
rather than ).

HTH
--
Kevin Vaughn


"Powlaz" wrote:

Kevin, Andy, thanks for the quick responses. Both of your formulas worked
(as far as I can tell). I'm interested in understanding how they work if you
could offer a quick explanation.

Moreso, I need a little more help. Both the formulas returned a 0 when
summing a range of blank cells. Here's the issue, this spreadsheet is pre -
formatted for a year's worth of data. I'd like for the cells in an unused
month to remain blank until the data is entered (and subsequently
calculated).

There is currently no user added data in cells b6:b12. But cell b13 that
calculates the total of b6:b12 shows 0. I'd like it to be blank until data
is entered into b6:b12. How can I concisely do that?

I thought about using IF and OR functions combined so that each cell is
checked for a value greater than "" before it will calculate the total of the
cells but it's very long and I think inefficient.

Got anything else?

Thanks

PO
"AndyB" wrote:

Hi

Try something like this:
=SUMPRODUCT((D7:G70)*(D7:G7))

AndyB

"Powlaz" wrote in message
...
I'm looking to total a range of cells ONLY if they have a value of 0 or
more
entered in them. In plain english:

Sumif A1 is =0
Sumif A2 is =0
Sumif A3 is =0

and so on. Where every cell in the range must have a value of 0 or more
entered before the function will return a result. How do I do this?

Thanks,

PO

Kevin,

Great - I get it. Thank you for the explanation. Works perfectly. I didn't
use a label (SumRange). I like to see the range in the equation. But I've
run into a little problem doing it that way. One part of the spreadsheet
requires me to use this statement for cells that are not in sequence. The
COUNTIF function will not allow more than 3 cell references, where I need 8
(b6,d6,f6,h6,j6 etc.) Is there a way to make COUNTIF accept more than 3 cell
references for cells that are not in sequence?

PO

"Kevin Vaughn" wrote:

=IF(COUNTIF(SumRange,"=0")=ROWS(SumRange),SUM(Sum Range),"")
My sumrange was a1:a10 so rows(sumrange) returns 10. The countif will count
the number of occurences where the cells in sumrange = 0. If all of the
cells meet the criteria, this will equal 10. Therefore the true portion of
the IF statement will be returned which is SUM(Sumrange). Otherwise ""
(nothing) will be returned. This per your latest request to have "" shown
rather than ).

HTH
--
Kevin Vaughn


"Powlaz" wrote:

Kevin, Andy, thanks for the quick responses. Both of your formulas worked
(as far as I can tell). I'm interested in understanding how they work if you
could offer a quick explanation.

Moreso, I need a little more help. Both the formulas returned a 0 when
summing a range of blank cells. Here's the issue, this spreadsheet is pre -
formatted for a year's worth of data. I'd like for the cells in an unused
month to remain blank until the data is entered (and subsequently
calculated).

There is currently no user added data in cells b6:b12. But cell b13 that
calculates the total of b6:b12 shows 0. I'd like it to be blank until data
is entered into b6:b12. How can I concisely do that?

I thought about using IF and OR functions combined so that each cell is
checked for a value greater than "" before it will calculate the total of the
cells but it's very long and I think inefficient.

Got anything else?

Thanks

PO
"AndyB" wrote:

Hi

Try something like this:
=SUMPRODUCT((D7:G70)*(D7:G7))

AndyB

"Powlaz" wrote in message
...
I'm looking to total a range of cells ONLY if they have a value of 0 or
more
entered in them. In plain english:

Sumif A1 is =0
Sumif A2 is =0
Sumif A3 is =0

and so on. Where every cell in the range must have a value of 0 or more
entered before the function will return a result. How do I do this?

Thanks,

PO

Kevin,

I looked at everything again and I'm afraid I'm not where I need to be.
Bear with me. I'm not sure if I worded my question well enough.

I have a table in which each column represents a month. The first line of
each column is a total of the 7 lines below it. The last column is an
average of each monthly total.

1st. I'd like to keep a running average of the totals in each month. If
there is no value entered in the 'Total' cell for a month I don't want "0" to
show up. Using the statement you gave me before all of my 0's went away when
the 'Total' cells were blank. When I entered a total into January (b6) the
average calcualted in AA6. But when I entered a total into February (d6) the
average in AA6 disappeared. What's the fix?

2nd. I'd like to keep a running total of jobs for the month. In January
these would be cells a7:a13. If I just use SUM there is a 0 in a14 until
data is entered into one of the cells a7:a13. I'd like a14 to stay blank and
calculate a total as each cell is populated:

a7 = 5
a14= 5

a7=5
a8=5
a14=10 ---and so on.

What am I missing?

PO

"Kevin Vaughn" wrote:

=IF(COUNTIF(SumRange,"=0")=ROWS(SumRange),SUM(Sum Range),"")
My sumrange was a1:a10 so rows(sumrange) returns 10. The countif will count
the number of occurences where the cells in sumrange = 0. If all of the
cells meet the criteria, this will equal 10. Therefore the true portion of
the IF statement will be returned which is SUM(Sumrange). Otherwise ""
(nothing) will be returned. This per your latest request to have "" shown
rather than ).

HTH
--
Kevin Vaughn


"Powlaz" wrote:

Kevin, Andy, thanks for the quick responses. Both of your formulas worked
(as far as I can tell). I'm interested in understanding how they work if you
could offer a quick explanation.

Moreso, I need a little more help. Both the formulas returned a 0 when
summing a range of blank cells. Here's the issue, this spreadsheet is pre -
formatted for a year's worth of data. I'd like for the cells in an unused
month to remain blank until the data is entered (and subsequently
calculated).

There is currently no user added data in cells b6:b12. But cell b13 that
calculates the total of b6:b12 shows 0. I'd like it to be blank until data
is entered into b6:b12. How can I concisely do that?

I thought about using IF and OR functions combined so that each cell is
checked for a value greater than "" before it will calculate the total of the
cells but it's very long and I think inefficient.

Got anything else?

Thanks

PO
"AndyB" wrote:

Hi

Try something like this:
=SUMPRODUCT((D7:G70)*(D7:G7))

AndyB

"Powlaz" wrote in message
...
I'm looking to total a range of cells ONLY if they have a value of 0 or
more
entered in them. In plain english:

Sumif A1 is =0
Sumif A2 is =0
Sumif A3 is =0

and so on. Where every cell in the range must have a value of 0 or more
entered before the function will return a result. How do I do this?

Thanks,

PO

I don't know why your average formula would go blank. What is the formula in
AA6?

For your 2nd question, this formula should work:

=IF(COUNT(A8:A13)0,SUM(A8:A13),"")

--
Kevin Vaughn


"Powlaz" wrote:

Kevin,

I looked at everything again and I'm afraid I'm not where I need to be.
Bear with me. I'm not sure if I worded my question well enough.

I have a table in which each column represents a month. The first line of
each column is a total of the 7 lines below it. The last column is an
average of each monthly total.

1st. I'd like to keep a running average of the totals in each month. If
there is no value entered in the 'Total' cell for a month I don't want "0" to
show up. Using the statement you gave me before all of my 0's went away when
the 'Total' cells were blank. When I entered a total into January (b6) the
average calcualted in AA6. But when I entered a total into February (d6) the
average in AA6 disappeared. What's the fix?

2nd. I'd like to keep a running total of jobs for the month. In January
these would be cells a7:a13. If I just use SUM there is a 0 in a14 until
data is entered into one of the cells a7:a13. I'd like a14 to stay blank and
calculate a total as each cell is populated:

a7 = 5
a14= 5

a7=5
a8=5
a14=10 ---and so on.

What am I missing?

PO

"Kevin Vaughn" wrote:

=IF(COUNTIF(SumRange,"=0")=ROWS(SumRange),SUM(Sum Range),"")
My sumrange was a1:a10 so rows(sumrange) returns 10. The countif will count
the number of occurences where the cells in sumrange = 0. If all of the
cells meet the criteria, this will equal 10. Therefore the true portion of
the IF statement will be returned which is SUM(Sumrange). Otherwise ""
(nothing) will be returned. This per your latest request to have "" shown
rather than ).

HTH
--
Kevin Vaughn


"Powlaz" wrote:

Kevin, Andy, thanks for the quick responses. Both of your formulas worked
(as far as I can tell). I'm interested in understanding how they work if you
could offer a quick explanation.

Moreso, I need a little more help. Both the formulas returned a 0 when
summing a range of blank cells. Here's the issue, this spreadsheet is pre -
formatted for a year's worth of data. I'd like for the cells in an unused
month to remain blank until the data is entered (and subsequently
calculated).

There is currently no user added data in cells b6:b12. But cell b13 that
calculates the total of b6:b12 shows 0. I'd like it to be blank until data
is entered into b6:b12. How can I concisely do that?

I thought about using IF and OR functions combined so that each cell is
checked for a value greater than "" before it will calculate the total of the
cells but it's very long and I think inefficient.

Got anything else?

Thanks

PO
"AndyB" wrote:

Hi

Try something like this:
=SUMPRODUCT((D7:G70)*(D7:G7))

AndyB

"Powlaz" wrote in message
...
I'm looking to total a range of cells ONLY if they have a value of 0 or
more
entered in them. In plain english:

Sumif A1 is =0
Sumif A2 is =0
Sumif A3 is =0

and so on. Where every cell in the range must have a value of 0 or more
entered before the function will return a result. How do I do this?

Thanks,

PO

The average formula in AA5 is this:

=IF(COUNTIF(B5:Y5,"=0")=ROWS(B5:Y5),AVERAGE(B5:Y5 ),"")

When all cells from B5:Y5 are blank and Average is not calculated.
When data is entered into B5 (January) AA5 calculates
When data is entered into D5 (February) AA5 goes blank

I've used several much simpler statements but get stuck with a #DIV/0! error
in this same field.

You know what I'm going for. I wish I could upload the spreadsheet. There
are a total of 8 fields in each month that need to be populated. All the
remaining fields should perform calculations on the data that is entered into
these fields. If there is nothing to calculate then everything should be
blank. Not have 0's, or $ -, or DIV/0 errors and so on.

On a much smaller scale I was able to use simple IF statements to prevent a
calculation of the adjacent field wasn't populated. These broader scale
calculations are driving me nuts.

Looks to me like your reply to my 2nd question worked. I'm not sure how or
why but it did what I needed. Perhaps I can COUNT or COUNTA for the rest of
my calculations.

Thanks for helping me. I'll keep my eyes peeled for your next post.

PO

"Kevin Vaughn" wrote:

I don't know why your average formula would go blank. What is the formula in
AA6?

For your 2nd question, this formula should work:

=IF(COUNT(A8:A13)0,SUM(A8:A13),"")

--
Kevin Vaughn


"Powlaz" wrote:

Kevin,

I looked at everything again and I'm afraid I'm not where I need to be.
Bear with me. I'm not sure if I worded my question well enough.

I have a table in which each column represents a month. The first line of
each column is a total of the 7 lines below it. The last column is an
average of each monthly total.

1st. I'd like to keep a running average of the totals in each month. If
there is no value entered in the 'Total' cell for a month I don't want "0" to
show up. Using the statement you gave me before all of my 0's went away when
the 'Total' cells were blank. When I entered a total into January (b6) the
average calcualted in AA6. But when I entered a total into February (d6) the
average in AA6 disappeared. What's the fix?

2nd. I'd like to keep a running total of jobs for the month. In January
these would be cells a7:a13. If I just use SUM there is a 0 in a14 until
data is entered into one of the cells a7:a13. I'd like a14 to stay blank and
calculate a total as each cell is populated:

a7 = 5
a14= 5

a7=5
a8=5
a14=10 ---and so on.

What am I missing?

PO

"Kevin Vaughn" wrote:

=IF(COUNTIF(SumRange,"=0")=ROWS(SumRange),SUM(Sum Range),"")
My sumrange was a1:a10 so rows(sumrange) returns 10. The countif will count
the number of occurences where the cells in sumrange = 0. If all of the
cells meet the criteria, this will equal 10. Therefore the true portion of
the IF statement will be returned which is SUM(Sumrange). Otherwise ""
(nothing) will be returned. This per your latest request to have "" shown
rather than ).

HTH
--
Kevin Vaughn


"Powlaz" wrote:

Kevin, Andy, thanks for the quick responses. Both of your formulas worked
(as far as I can tell). I'm interested in understanding how they work if you
could offer a quick explanation.

Moreso, I need a little more help. Both the formulas returned a 0 when
summing a range of blank cells. Here's the issue, this spreadsheet is pre -
formatted for a year's worth of data. I'd like for the cells in an unused
month to remain blank until the data is entered (and subsequently
calculated).

There is currently no user added data in cells b6:b12. But cell b13 that
calculates the total of b6:b12 shows 0. I'd like it to be blank until data
is entered into b6:b12. How can I concisely do that?

I thought about using IF and OR functions combined so that each cell is
checked for a value greater than "" before it will calculate the total of the
cells but it's very long and I think inefficient.

Got anything else?

Thanks

PO
"AndyB" wrote:

Hi

Try something like this:
=SUMPRODUCT((D7:G70)*(D7:G7))

AndyB

"Powlaz" wrote in message
...
I'm looking to total a range of cells ONLY if they have a value of 0 or
more
entered in them. In plain english:

Sumif A1 is =0
Sumif A2 is =0
Sumif A3 is =0

and so on. Where every cell in the range must have a value of 0 or more
entered before the function will return a result. How do I do this?

Thanks,

PO

I don't know if this will solve all of your problems, but you probably want
to change the formula to the below and see if that gets you going in the
right direction:

=IF(COUNTIF(B5:Y5,"=0")=COLUMNS(B5:Y5),AVERAGE(B5 :Y5),"")

You are now dealing with a count of columns rather than rows (as you are
going across, not down.)

I don't know if this will completely solve your problems as you seem to want
to skip every other column.
--
Kevin Vaughn


"Powlaz" wrote:

The average formula in AA5 is this:

=IF(COUNTIF(B5:Y5,"=0")=ROWS(B5:Y5),AVERAGE(B5:Y5 ),"")

When all cells from B5:Y5 are blank and Average is not calculated.
When data is entered into B5 (January) AA5 calculates
When data is entered into D5 (February) AA5 goes blank

I've used several much simpler statements but get stuck with a #DIV/0! error
in this same field.

You know what I'm going for. I wish I could upload the spreadsheet. There
are a total of 8 fields in each month that need to be populated. All the
remaining fields should perform calculations on the data that is entered into
these fields. If there is nothing to calculate then everything should be
blank. Not have 0's, or $ -, or DIV/0 errors and so on.

On a much smaller scale I was able to use simple IF statements to prevent a
calculation of the adjacent field wasn't populated. These broader scale
calculations are driving me nuts.

Looks to me like your reply to my 2nd question worked. I'm not sure how or
why but it did what I needed. Perhaps I can COUNT or COUNTA for the rest of
my calculations.

Thanks for helping me. I'll keep my eyes peeled for your next post.

PO

"Kevin Vaughn" wrote:

I don't know why your average formula would go blank. What is the formula in
AA6?

For your 2nd question, this formula should work:

=IF(COUNT(A8:A13)0,SUM(A8:A13),"")

--
Kevin Vaughn


"Powlaz" wrote:

Kevin,

I looked at everything again and I'm afraid I'm not where I need to be.
Bear with me. I'm not sure if I worded my question well enough.

I have a table in which each column represents a month. The first line of
each column is a total of the 7 lines below it. The last column is an
average of each monthly total.

1st. I'd like to keep a running average of the totals in each month. If
there is no value entered in the 'Total' cell for a month I don't want "0" to
show up. Using the statement you gave me before all of my 0's went away when
the 'Total' cells were blank. When I entered a total into January (b6) the
average calcualted in AA6. But when I entered a total into February (d6) the
average in AA6 disappeared. What's the fix?

2nd. I'd like to keep a running total of jobs for the month. In January
these would be cells a7:a13. If I just use SUM there is a 0 in a14 until
data is entered into one of the cells a7:a13. I'd like a14 to stay blank and
calculate a total as each cell is populated:

a7 = 5
a14= 5

a7=5
a8=5
a14=10 ---and so on.

What am I missing?

PO

"Kevin Vaughn" wrote:

=IF(COUNTIF(SumRange,"=0")=ROWS(SumRange),SUM(Sum Range),"")
My sumrange was a1:a10 so rows(sumrange) returns 10. The countif will count
the number of occurences where the cells in sumrange = 0. If all of the
cells meet the criteria, this will equal 10. Therefore the true portion of
the IF statement will be returned which is SUM(Sumrange). Otherwise ""
(nothing) will be returned. This per your latest request to have "" shown
rather than ).

HTH
--
Kevin Vaughn


"Powlaz" wrote:

Kevin, Andy, thanks for the quick responses. Both of your formulas worked
(as far as I can tell). I'm interested in understanding how they work if you
could offer a quick explanation.

Moreso, I need a little more help. Both the formulas returned a 0 when
summing a range of blank cells. Here's the issue, this spreadsheet is pre -
formatted for a year's worth of data. I'd like for the cells in an unused
month to remain blank until the data is entered (and subsequently
calculated).

There is currently no user added data in cells b6:b12. But cell b13 that
calculates the total of b6:b12 shows 0. I'd like it to be blank until data
is entered into b6:b12. How can I concisely do that?

I thought about using IF and OR functions combined so that each cell is
checked for a value greater than "" before it will calculate the total of the
cells but it's very long and I think inefficient.

Got anything else?

Thanks

PO
"AndyB" wrote:

Hi

Try something like this:
=SUMPRODUCT((D7:G70)*(D7:G7))

AndyB

"Powlaz" wrote in message
...
I'm looking to total a range of cells ONLY if they have a value of 0 or
more
entered in them. In plain english:

Sumif A1 is =0
Sumif A2 is =0
Sumif A3 is =0

and so on. Where every cell in the range must have a value of 0 or more
entered before the function will return a result. How do I do this?

Thanks,

PO

Kevin,

Your last reply did the job. I used a combination of the COUNTIF statement
and the COUNT statement you gave me and everything worked perfectly. The
COUNT statement really was the key. I had the logic, but didn't include the
COUNT function in my statement.

I skipped a row because the name of each month spans two merged cells. And
I named my data ranges (because they were every 2nd cell) which really
simplified things.

Now my spreadsheet is blank when it's empty, calculates some stuff as it is
entered and doesn't calculate other things until all cells in the range are
complete. It's perfect.

I guess I don't really understand the power of some of the functions so I'm
thinking I need a book or tutorial site to get a little further up to speed.

Thanks again!

Matt

"Kevin Vaughn" wrote:

I don't know if this will solve all of your problems, but you probably want
to change the formula to the below and see if that gets you going in the
right direction:

=IF(COUNTIF(B5:Y5,"=0")=COLUMNS(B5:Y5),AVERAGE(B5 :Y5),"")

You are now dealing with a count of columns rather than rows (as you are
going across, not down.)

I don't know if this will completely solve your problems as you seem to want
to skip every other column.
--
Kevin Vaughn


"Powlaz" wrote:

The average formula in AA5 is this:

=IF(COUNTIF(B5:Y5,"=0")=ROWS(B5:Y5),AVERAGE(B5:Y5 ),"")

When all cells from B5:Y5 are blank and Average is not calculated.
When data is entered into B5 (January) AA5 calculates
When data is entered into D5 (February) AA5 goes blank

I've used several much simpler statements but get stuck with a #DIV/0! error
in this same field.

You know what I'm going for. I wish I could upload the spreadsheet. There
are a total of 8 fields in each month that need to be populated. All the
remaining fields should perform calculations on the data that is entered into
these fields. If there is nothing to calculate then everything should be
blank. Not have 0's, or $ -, or DIV/0 errors and so on.

On a much smaller scale I was able to use simple IF statements to prevent a
calculation of the adjacent field wasn't populated. These broader scale
calculations are driving me nuts.

Looks to me like your reply to my 2nd question worked. I'm not sure how or
why but it did what I needed. Perhaps I can COUNT or COUNTA for the rest of
my calculations.

Thanks for helping me. I'll keep my eyes peeled for your next post.

PO

"Kevin Vaughn" wrote:

I don't know why your average formula would go blank. What is the formula in
AA6?

For your 2nd question, this formula should work:

=IF(COUNT(A8:A13)0,SUM(A8:A13),"")

--
Kevin Vaughn


"Powlaz" wrote:

Kevin,

I looked at everything again and I'm afraid I'm not where I need to be.
Bear with me. I'm not sure if I worded my question well enough.

I have a table in which each column represents a month. The first line of
each column is a total of the 7 lines below it. The last column is an
average of each monthly total.

1st. I'd like to keep a running average of the totals in each month. If
there is no value entered in the 'Total' cell for a month I don't want "0" to
show up. Using the statement you gave me before all of my 0's went away when
the 'Total' cells were blank. When I entered a total into January (b6) the
average calcualted in AA6. But when I entered a total into February (d6) the
average in AA6 disappeared. What's the fix?

2nd. I'd like to keep a running total of jobs for the month. In January
these would be cells a7:a13. If I just use SUM there is a 0 in a14 until
data is entered into one of the cells a7:a13. I'd like a14 to stay blank and
calculate a total as each cell is populated:

a7 = 5
a14= 5

a7=5
a8=5
a14=10 ---and so on.

What am I missing?

PO

"Kevin Vaughn" wrote:

=IF(COUNTIF(SumRange,"=0")=ROWS(SumRange),SUM(Sum Range),"")
My sumrange was a1:a10 so rows(sumrange) returns 10. The countif will count
the number of occurences where the cells in sumrange = 0. If all of the
cells meet the criteria, this will equal 10. Therefore the true portion of
the IF statement will be returned which is SUM(Sumrange). Otherwise ""
(nothing) will be returned. This per your latest request to have "" shown
rather than ).

HTH
--
Kevin Vaughn


"Powlaz" wrote:

Kevin, Andy, thanks for the quick responses. Both of your formulas worked
(as far as I can tell). I'm interested in understanding how they work if you
could offer a quick explanation.

Moreso, I need a little more help. Both the formulas returned a 0 when
summing a range of blank cells. Here's the issue, this spreadsheet is pre -
formatted for a year's worth of data. I'd like for the cells in an unused
month to remain blank until the data is entered (and subsequently
calculated).

There is currently no user added data in cells b6:b12. But cell b13 that
calculates the total of b6:b12 shows 0. I'd like it to be blank until data
is entered into b6:b12. How can I concisely do that?

I thought about using IF and OR functions combined so that each cell is
checked for a value greater than "" before it will calculate the total of the
cells but it's very long and I think inefficient.

Got anything else?

Thanks

PO
"AndyB" wrote:

Hi

Try something like this:
=SUMPRODUCT((D7:G70)*(D7:G7))

AndyB

"Powlaz" wrote in message
...
I'm looking to total a range of cells ONLY if they have a value of 0 or
more
entered in them. In plain english:

Sumif A1 is =0
Sumif A2 is =0
Sumif A3 is =0

and so on. Where every cell in the range must have a value of 0 or more
entered before the function will return a result. How do I do this?

Thanks,

PO

You're welcome. Glad you got everything working. You mentioned reading a
book, so I thought I would recommend Excel 2003 formulas by John Walkenbach.
And although I haven't read it (or even seen it at the bookstore,) there is a
book by Bob Umlas I believe called something along the lines of This isn't
Excel, it's magic (let me see if a search will confirm this ...

"This isn't Excel, It's Magic!" by Bob
Umlas.

Close, I didn't get the !. I have read a paper by him though on Array
formulae which I found very informative so the book is probably worth a read.
As for a tutorial site, a lot of the MVPs here have websites which if you
hang around long enough you are sure to get referred to. I have recently
started reading a daily dose of Excel, formerly Dick's blog as I understand
it. That seems to be a very helpful site.

http://www.dailydoseofexcel.com/

--
Kevin Vaughn


"Powlaz" wrote:

Kevin,

Your last reply did the job. I used a combination of the COUNTIF statement
and the COUNT statement you gave me and everything worked perfectly. The
COUNT statement really was the key. I had the logic, but didn't include the
COUNT function in my statement.

I skipped a row because the name of each month spans two merged cells. And
I named my data ranges (because they were every 2nd cell) which really
simplified things.

Now my spreadsheet is blank when it's empty, calculates some stuff as it is
entered and doesn't calculate other things until all cells in the range are
complete. It's perfect.

I guess I don't really understand the power of some of the functions so I'm
thinking I need a book or tutorial site to get a little further up to speed.

Thanks again!

Matt

"Kevin Vaughn" wrote:

I don't know if this will solve all of your problems, but you probably want
to change the formula to the below and see if that gets you going in the
right direction:

=IF(COUNTIF(B5:Y5,"=0")=COLUMNS(B5:Y5),AVERAGE(B5 :Y5),"")

You are now dealing with a count of columns rather than rows (as you are
going across, not down.)

I don't know if this will completely solve your problems as you seem to want
to skip every other column.
--
Kevin Vaughn


"Powlaz" wrote:

The average formula in AA5 is this:

=IF(COUNTIF(B5:Y5,"=0")=ROWS(B5:Y5),AVERAGE(B5:Y5 ),"")

When all cells from B5:Y5 are blank and Average is not calculated.
When data is entered into B5 (January) AA5 calculates
When data is entered into D5 (February) AA5 goes blank

I've used several much simpler statements but get stuck with a #DIV/0! error
in this same field.

You know what I'm going for. I wish I could upload the spreadsheet. There
are a total of 8 fields in each month that need to be populated. All the
remaining fields should perform calculations on the data that is entered into
these fields. If there is nothing to calculate then everything should be
blank. Not have 0's, or $ -, or DIV/0 errors and so on.

On a much smaller scale I was able to use simple IF statements to prevent a
calculation of the adjacent field wasn't populated. These broader scale
calculations are driving me nuts.

Looks to me like your reply to my 2nd question worked. I'm not sure how or
why but it did what I needed. Perhaps I can COUNT or COUNTA for the rest of
my calculations.

Thanks for helping me. I'll keep my eyes peeled for your next post.

PO

"Kevin Vaughn" wrote:

I don't know why your average formula would go blank. What is the formula in
AA6?

For your 2nd question, this formula should work:

=IF(COUNT(A8:A13)0,SUM(A8:A13),"")

--
Kevin Vaughn


"Powlaz" wrote:

Kevin,

I looked at everything again and I'm afraid I'm not where I need to be.
Bear with me. I'm not sure if I worded my question well enough.

I have a table in which each column represents a month. The first line of
each column is a total of the 7 lines below it. The last column is an
average of each monthly total.

1st. I'd like to keep a running average of the totals in each month. If
there is no value entered in the 'Total' cell for a month I don't want "0" to
show up. Using the statement you gave me before all of my 0's went away when
the 'Total' cells were blank. When I entered a total into January (b6) the
average calcualted in AA6. But when I entered a total into February (d6) the
average in AA6 disappeared. What's the fix?

2nd. I'd like to keep a running total of jobs for the month. In January
these would be cells a7:a13. If I just use SUM there is a 0 in a14 until
data is entered into one of the cells a7:a13. I'd like a14 to stay blank and
calculate a total as each cell is populated:

a7 = 5
a14= 5

a7=5
a8=5
a14=10 ---and so on.

What am I missing?

PO

"Kevin Vaughn" wrote:

=IF(COUNTIF(SumRange,"=0")=ROWS(SumRange),SUM(Sum Range),"")
My sumrange was a1:a10 so rows(sumrange) returns 10. The countif will count
the number of occurences where the cells in sumrange = 0. If all of the
cells meet the criteria, this will equal 10. Therefore the true portion of
the IF statement will be returned which is SUM(Sumrange). Otherwise ""
(nothing) will be returned. This per your latest request to have "" shown
rather than ).

HTH
--
Kevin Vaughn


"Powlaz" wrote:

Kevin, Andy, thanks for the quick responses. Both of your formulas worked
(as far as I can tell). I'm interested in understanding how they work if you
could offer a quick explanation.

Moreso, I need a little more help. Both the formulas returned a 0 when
summing a range of blank cells. Here's the issue, this spreadsheet is pre -
formatted for a year's worth of data. I'd like for the cells in an unused
month to remain blank until the data is entered (and subsequently
calculated).

There is currently no user added data in cells b6:b12. But cell b13 that
calculates the total of b6:b12 shows 0. I'd like it to be blank until data
is entered into b6:b12. How can I concisely do that?

I thought about using IF and OR functions combined so that each cell is
checked for a value greater than "" before it will calculate the total of the
cells but it's very long and I think inefficient.

Got anything else?

Thanks

PO
"AndyB" wrote:

Hi

Try something like this:
=SUMPRODUCT((D7:G70)*(D7:G7))

AndyB

"Powlaz" wrote in message
...
I'm looking to total a range of cells ONLY if they have a value of 0 or
more
entered in them. In plain english:

Sumif A1 is =0
Sumif A2 is =0
Sumif A3 is =0

and so on. Where every cell in the range must have a value of 0 or more
entered before the function will return a result. How do I do this?

Thanks,

PO