Is it possible to SUM based on value of another column?
 

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?

Is it possible to SUM based on value of another column?
 

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?

Is it possible to SUM based on value of another column?
 

=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?

Is it possible to SUM based on value of another column?
 

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?

Is it possible to SUM based on value of another column?
 

Thanks everyone for the quick responses!

This newsgroup rocks!