Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible to SUM based on value of another column?
Thanks everyone for the quick responses!
This newsgroup rocks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Based on Mulipple Criteria acrossed Column count last column | Excel Worksheet Functions | |||
Adding a Custom Column based on an existing Column (EXPERT) | Excel Worksheet Functions | |||
Count number of cells and total in one column, based on another column suffix | Excel Worksheet Functions | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
move contents of column C based on criteria related to column A | Excel Discussion (Misc queries) |