Sum total in S7:S2000 w/ a twist

[James]

Hi,

I need to sum a total in Column S7:S2000 only
if Column Q7:Q2000 is populated(has a number
in it)the sum total will reside in S6,
so it would be something like this:
=Sum(S7:S2000) if Q7:Q2000 is populated,if
any cell in Q7:Q2000 is empty then the cell
in Column S is not added to the total.

Any help on this would be appreciated

Thanks James

 

[Juan Pablo González]

Take a look at SUMIF Worksheet Function in the Help Files.

 

[Barrie Davidson]

On 2002-02-18 11:56, James wrote:
Hi,

I need to sum a total in Column S7:S2000 only
if Column Q7:Q2000 is populated(has a number
in it)the sum total will reside in S6,
so it would be something like this:
=Sum(S7:S2000) if Q7:Q2000 is populated,if
any cell in Q7:Q2000 is empty then the cell
in Column S is not added to the total.

Any help on this would be appreciated

Thanks James

James, try this formula:

=SUM((S7:S2000)*(Q7:Q2000<>0))

and enter it as an array formula (instead of pressing ENTER, use CTRL+SHIFT+ENTER).

 

[Juan Pablo González]

Barrie, altough your formula works, I feel that there's no need for a CSE, since it's a SUM with only one condition --> SUMIF.

 

[Barrie Davidson]

You're absolutely correct! Couldn't see the forest for the trees

 

[Aladin Akyurek]

In S6 enter:

=(SUM(Q7:Q2000)>0)*(SUM(S7:S2000))

Aladin

 

[Mark W.]

On 2002-02-18 11:56, James wrote:
Hi,

I need to sum a total in Column S7:S2000 only
if Column Q7:Q2000 is populated(has a number
in it)the sum total will reside in S6,
so it would be something like this:
=Sum(S7:S2000) if Q7:Q2000 is populated,if
any cell in Q7:Q2000 is empty then the cell
in Column S is not added to the total.

Any help on this would be appreciated

Thanks James

=SUMIF(Q7:Q2000,"<>",S7:S2000)

 

[dabblerj]

I've tried the solotion provided here, and I think it does not work on Excel 2007 as it did on older versions.

My situation is slightly different though, so it may just need a different formula.

I am trying to sum H7:I20 when D7:D20 is not blank. If a cell in D7:D20 is blank, then I don't want to sum any of the cells on that row (do not include H<row of blank> or I<row of blank>)

Because people keep breaking the formulas that use Ctrl+Shift+Enter, I'm trying to avoid this if at all possible.

I would appreciate any advice.

Thanks,
Michael

 

[Jonmo1]

Welcome to the board..

Try

=SUMPRODUCT((D7:D20<>"")*(H7:I20))

 

[Excelestial]

In Excel 2007, or any other version...use the simple formula in S6:

=SUMIF(Q7:Q2000,">0",S7:S2000) or SUMIF(Q7:Q2000,">""",S7:S2000)

with Q column as your criteria range, the operator ">0" as your criteria, and the S column as the values you want summed when the condition in Q is met.