I am doing a SUM of values in a column starting at row 6, so I use a formula
like this:

=SUM(I6:I9999)

I can't do I:I because there are irrelevant numbers located in some of the
rows of column I above row 6. I took an arbitrary number 9999 because I know
the rows will never go near that number.

Anyway, I need to add a restriction to this formula and don't know how

I need it to exclude numbers in rows which have a value of "F" in a
different column (column B)

So for example it starts summing up values from I6, I7, I8 etc then lets say
on I10 it finds that in column B10 there is a value of "F". so it does not
add the value of I10 to the SUM

Is this possible?

Hi,
try
=SUMPRODUCT(--(b6:b99999<"F"),i6:i99999)

"trant" wrote:

I am doing a SUM of values in a column starting at row 6, so I use a formula
like this:

=SUM(I6:I9999)

I can't do I:I because there are irrelevant numbers located in some of the
rows of column I above row 6. I took an arbitrary number 9999 because I know
the rows will never go near that number.

Anyway, I need to add a restriction to this formula and don't know how

I need it to exclude numbers in rows which have a value of "F" in a
different column (column B)

So for example it starts summing up values from I6, I7, I8 etc then lets say
on I10 it finds that in column B10 there is a value of "F". so it does not
add the value of I10 to the SUM

Is this possible?

=SUM(F6:F9999)-SUMIF(B6:F9999,"F",F6:F9999) will sum the entire F column, but
then subtracts the cells where Col B has an "F".

--

HTH

Kassie

Replace xxx with hotmail


"trant" wrote:

I am doing a SUM of values in a column starting at row 6, so I use a formula
like this:

=SUM(I6:I9999)

I can't do I:I because there are irrelevant numbers located in some of the
rows of column I above row 6. I took an arbitrary number 9999 because I know
the rows will never go near that number.

Anyway, I need to add a restriction to this formula and don't know how

I need it to exclude numbers in rows which have a value of "F" in a
different column (column B)

So for example it starts summing up values from I6, I7, I8 etc then lets say
on I10 it finds that in column B10 there is a value of "F". so it does not
add the value of I10 to the SUM

Is this possible?

Certainly! The SUMIF function works on this exact principle.

=SUMIF(B6:B9999,"<F",I6:I9999)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"trant" wrote:

I am doing a SUM of values in a column starting at row 6, so I use a formula
like this:

=SUM(I6:I9999)

I can't do I:I because there are irrelevant numbers located in some of the
rows of column I above row 6. I took an arbitrary number 9999 because I know
the rows will never go near that number.

Anyway, I need to add a restriction to this formula and don't know how

I need it to exclude numbers in rows which have a value of "F" in a
different column (column B)

So for example it starts summing up values from I6, I7, I8 etc then lets say
on I10 it finds that in column B10 there is a value of "F". so it does not
add the value of I10 to the SUM

Is this possible?

Thanks everyone for the quick responses!

This newsgroup rocks!