Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum multiple arrays of data according to criteria
I have had a good look through the posts but haven't found a problem
quite like this, though I am sure it has come up before. Are you able to obtain a single result for the following problem. For each car manufacturer, sum all the months of data, excluding the comment columns, for any row that does not contain the phrase deleted. But I don't want to use a pivot table or VBA. May June JulyComments Comments Aug Sep ford 12 1 101 102 87 ford 168 2 102 87 16 chrysler 44 5 105 74 31 chrysler 54 3 118 166 18 deleted 73 7 147 518 35 deleted 89 9 198 2 68 ferrari 28 5 187 87 18 ferrari 46 1 185 89 82 My problem is how to sum the row without the columns in the middle which cannot be moved. And how to search and sum row by row without using VBA. thanks in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum multiple arrays of data according to criteria
With your posted table of information in cells A1:H9
This formula returns the total values for Ford F1: Ford G1: =SUMPRODUCT(($A$2:$A$9=J1)*$B$2:$H$9) OR...if you want the total of all rows that do not begin with "delted" G1: =SUMPRODUCT(($A$2:$A$9<"deleted")*$B$2:$H$9) Is that something you can work with? (Post back if you have more questions.) *********** Regards, Ron XL2003, WinXP " wrote: I have had a good look through the posts but haven't found a problem quite like this, though I am sure it has come up before. Are you able to obtain a single result for the following problem. For each car manufacturer, sum all the months of data, excluding the comment columns, for any row that does not contain the phrase deleted. But I don't want to use a pivot table or VBA. May June JulyComments Comments Aug Sep ford 12 1 101 102 87 ford 168 2 102 87 16 chrysler 44 5 105 74 31 chrysler 54 3 118 166 18 deleted 73 7 147 518 35 deleted 89 9 198 2 68 ferrari 28 5 187 87 18 ferrari 46 1 185 89 82 My problem is how to sum the row without the columns in the middle which cannot be moved. And how to search and sum row by row without using VBA. thanks in advance |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum multiple arrays of data according to criteria
Additional info:
If there may be text in the sum range, then try these ARRAY FORMULAS (committed with Ctrl+Shift+Enter, instead of just Enter): Using my previous post's example.... The Ford total G1: =SUMPRODUCT(($A$2:$A$9=J1)*IF(ISNUMBER($B$2:$H$9), $B$2:$H$9)) or....the non "deleted" total =SUMPRODUCT(($A$2:$A$9<"deleted")*IF(ISNUMBER($B$ 2:$H$9),$B$2:$H$9)) Does that help? *********** Regards, Ron XL2003, WinXP "Ron Coderre" wrote: With your posted table of information in cells A1:H9 This formula returns the total values for Ford F1: Ford G1: =SUMPRODUCT(($A$2:$A$9=J1)*$B$2:$H$9) OR...if you want the total of all rows that do not begin with "delted" G1: =SUMPRODUCT(($A$2:$A$9<"deleted")*$B$2:$H$9) Is that something you can work with? (Post back if you have more questions.) *********** Regards, Ron XL2003, WinXP " wrote: I have had a good look through the posts but haven't found a problem quite like this, though I am sure it has come up before. Are you able to obtain a single result for the following problem. For each car manufacturer, sum all the months of data, excluding the comment columns, for any row that does not contain the phrase deleted. But I don't want to use a pivot table or VBA. May June JulyComments Comments Aug Sep ford 12 1 101 102 87 ford 168 2 102 87 16 chrysler 44 5 105 74 31 chrysler 54 3 118 166 18 deleted 73 7 147 518 35 deleted 89 9 198 2 68 ferrari 28 5 187 87 18 ferrari 46 1 185 89 82 My problem is how to sum the row without the columns in the middle which cannot be moved. And how to search and sum row by row without using VBA. thanks in advance |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum multiple arrays of data according to criteria
On 24 Jul, 17:38, Ron Coderre
wrote: With your posted table of information in cells A1:H9 This formula returns the total values for Ford F1: Ford G1: =SUMPRODUCT(($A$2:$A$9=J1)*$B$2:$H$9) OR...if you want the total of all rows that do not begin with "delted" G1: =SUMPRODUCT(($A$2:$A$9<"deleted")*$B$2:$H$9) Is that something you can work with? (Post back if you have more questions.) *********** Regards, Ron XL2003, WinXP " wrote: I have had a good look through the posts but haven't found a problem quite like this, though I am sure it has come up before. Are you able to obtain a single result for the following problem. For each car manufacturer, sum all the months of data, excluding the comment columns, for any row that does not contain the phrase deleted. But I don't want to use a pivot table or VBA. May June JulyComments Comments Aug Sep ford 12 1 101 102 87 ford 168 2 102 87 16 chrysler 44 5 105 74 31 chrysler 54 3 118 166 18 deleted 73 7 147 518 35 deleted 89 9 198 2 68 ferrari 28 5 187 87 18 ferrari 46 1 185 89 82 My problem is how to sum the row without the columns in the middle which cannot be moved. And how to search and sum row by row without using VBA. thanks in advance- Hide quoted text - - Show quoted text - thanks Ron Your formula works well for the table above. However, I drew up this table a little hastily. The comments columns actually contain numberical data. So the formula will include those columns, won't it? How do I exclude those columns? Thanks again for your help. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum multiple arrays of data according to criteria
Hmmm....I guessed the other way...that the Comments columns would contain text.
See if these ARRAY FORMULAS (that skip the comments column completely) help: If there may be text anyplace in the sum range G1: =SUMPRODUCT(($A$2:$A$9=J1)*($B$1:$H$1<"Comments") *IF(ISNUMBER($B$2:$H$9),$B$2:$H$9)) or =SUMPRODUCT(($A$2:$A$9<"deleted")*($B$1:$H$1<"Co mments")*IF(ISNUMBER($B$2:$H$9),$B$2:$H$9)) OR.....if there will NEVER be text in the sum range, then try thes REGULAR FORMULAS (committed with just Enter): G1: =SUMPRODUCT(($A$2:$A$9=J1)*($B$1:$H$1<"Comments") *$B$2:$H$9) or =SUMPRODUCT(($A$2:$A$9<"deleted")*($B$1:$H$1<"Co mments")*$B$2:$H$9) Does that help? *********** Regards, Ron XL2003, WinXP " wrote: On 24 Jul, 17:38, Ron Coderre wrote: With your posted table of information in cells A1:H9 This formula returns the total values for Ford F1: Ford G1: =SUMPRODUCT(($A$2:$A$9=J1)*$B$2:$H$9) OR...if you want the total of all rows that do not begin with "delted" G1: =SUMPRODUCT(($A$2:$A$9<"deleted")*$B$2:$H$9) Is that something you can work with? (Post back if you have more questions.) *********** Regards, Ron XL2003, WinXP " wrote: I have had a good look through the posts but haven't found a problem quite like this, though I am sure it has come up before. Are you able to obtain a single result for the following problem. For each car manufacturer, sum all the months of data, excluding the comment columns, for any row that does not contain the phrase deleted. But I don't want to use a pivot table or VBA. May June JulyComments Comments Aug Sep ford 12 1 101 102 87 ford 168 2 102 87 16 chrysler 44 5 105 74 31 chrysler 54 3 118 166 18 deleted 73 7 147 518 35 deleted 89 9 198 2 68 ferrari 28 5 187 87 18 ferrari 46 1 185 89 82 My problem is how to sum the row without the columns in the middle which cannot be moved. And how to search and sum row by row without using VBA. thanks in advance- Hide quoted text - - Show quoted text - thanks Ron Your formula works well for the table above. However, I drew up this table a little hastily. The comments columns actually contain numberical data. So the formula will include those columns, won't it? How do I exclude those columns? Thanks again for your help. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum multiple arrays of data according to criteria
On 24 Jul, 18:04, Ron Coderre
wrote: Hmmm....I guessed the other way...that the Comments columns would contain text. See if these ARRAY FORMULAS (that skip the comments column completely) help: If there may be text anyplace in the sum range G1: =SUMPRODUCT(($A$2:$A$9=J1)*($B$1:$H$1<"Comments") *IF(ISNUMBER($B$2:$H$9),$*B$2:$H$9)) or =SUMPRODUCT(($A$2:$A$9<"deleted")*($B$1:$H$1<"Co mments")*IF(ISNUMBER($B$2*:$H$9),$B$2:$H$9)) OR.....if there will NEVER be text in the sum range, then try thes REGULAR FORMULAS (committed with just Enter): G1: =SUMPRODUCT(($A$2:$A$9=J1)*($B$1:$H$1<"Comments") *$B$2:$H$9) or =SUMPRODUCT(($A$2:$A$9<"deleted")*($B$1:$H$1<"Co mments")*$B$2:$H$9) Does that help? *********** Regards, Ron XL2003, WinXP " wrote: On 24 Jul, 17:38, Ron Coderre wrote: With your posted table of information in cells A1:H9 This formula returns the total values for Ford F1: Ford G1: =SUMPRODUCT(($A$2:$A$9=J1)*$B$2:$H$9) OR...if you want the total of all rows that do not begin with "delted" G1: =SUMPRODUCT(($A$2:$A$9<"deleted")*$B$2:$H$9) Is that something you can work with? (Post back if you have more questions.) *********** Regards, Ron XL2003, WinXP " wrote: I have had a good look through the posts but haven't found a problem quite like this, though I am sure it has come up before. Are you able to obtain a single result for the following problem. For each car manufacturer, sum all the months of data, excluding the comment columns, for any row that does not contain the phrase deleted. But I don't want to use a pivot table or VBA. May June JulyComments Comments Aug Sep ford 12 1 101 102 87 ford 168 2 102 87 16 chrysler 44 5 105 74 31 chrysler 54 3 118 166 18 deleted 73 7 147 518 35 deleted 89 9 198 2 68 ferrari 28 5 187 87 18 ferrari 46 1 185 89 82 My problem is how to sum the row without the columns in the middle which cannot be moved. And how to search and sum row by row without using VBA. thanks in advance- Hide quoted text - - Show quoted text - thanks Ron Your formula works well for the table above. However, I drew up this table a little hastily. The comments columns actually contain numberical data. So the formula will include those columns, won't it? How do I exclude those columns? Thanks again for your help.- Hide quoted text - - Show quoted text - Hi Ron thanks again. I think my example wasn't as helpful as I had hoped. It's more of an example, the actual workbook I have contains 150 columns of numerical data and there is a group of about 25 columns in the middle which I want to exclude from the total. They have quite longwinded and different column headings so the method you suggested above wouldn't work. To be more specific there are many columns of different quarterly information and many rows of different contracts. Usually I would use a pivot table to summarise this but in this case i need a singe result returned by a formula. I want to sum those rows that do not contain the phrase deleted, and exclude a big middle group of columns that contain irrelevant quarterly data. Is there any way to do this? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum multiple arrays of data according to criteria
The simplest approach may be to simply flag the columns to be skipped by
entering the word "skip" in a row above or below the data..... In this example, I inserted a row above the data and entered "skip" in cells B1, E1 and F1: =SUMPRODUCT(($A$3:$A$10=J2)*($B$1:$H$1<"skip")*$B $3:$H$10) Alternatively, you can specify which columns to skip within the formula. If the list is short.....in this case, I skip Columns 5 and 6: G1: =SUMPRODUCT(($A$2:$A$9<"deleted")*ISNA(MATCH(COLU MN($B$2:$H$9),{5;6},0))*$B$2:$H$9) or....if the list is varied and long, put the list of column numbers in a range off to the side (I'll use P1:P10) and use this formula: G1: =SUMPRODUCT(($A$2:$A$9<"deleted")*ISNA(MATCH(COLU MN($B$2:$H$9),$P$1:$P$10,0))*$B$2:$H$9) Am I helping yet? *********** Regards, Ron XL2003, WinXP " wrote: On 24 Jul, 18:04, Ron Coderre wrote: Hmmm....I guessed the other way...that the Comments columns would contain text. See if these ARRAY FORMULAS (that skip the comments column completely) help: If there may be text anyplace in the sum range G1: =SUMPRODUCT(($A$2:$A$9=J1)*($B$1:$H$1<"Comments") *IF(ISNUMBER($B$2:$H$9),$-B$2:$H$9)) or =SUMPRODUCT(($A$2:$A$9<"deleted")*($B$1:$H$1<"Co mments")*IF(ISNUMBER($B$2-:$H$9),$B$2:$H$9)) OR.....if there will NEVER be text in the sum range, then try thes REGULAR FORMULAS (committed with just Enter): G1: =SUMPRODUCT(($A$2:$A$9=J1)*($B$1:$H$1<"Comments") *$B$2:$H$9) or =SUMPRODUCT(($A$2:$A$9<"deleted")*($B$1:$H$1<"Co mments")*$B$2:$H$9) Does that help? *********** Regards, Ron XL2003, WinXP " wrote: On 24 Jul, 17:38, Ron Coderre wrote: With your posted table of information in cells A1:H9 This formula returns the total values for Ford F1: Ford G1: =SUMPRODUCT(($A$2:$A$9=J1)*$B$2:$H$9) OR...if you want the total of all rows that do not begin with "delted" G1: =SUMPRODUCT(($A$2:$A$9<"deleted")*$B$2:$H$9) Is that something you can work with? (Post back if you have more questions.) *********** Regards, Ron XL2003, WinXP " wrote: I have had a good look through the posts but haven't found a problem quite like this, though I am sure it has come up before. Are you able to obtain a single result for the following problem. For each car manufacturer, sum all the months of data, excluding the comment columns, for any row that does not contain the phrase deleted. But I don't want to use a pivot table or VBA. May June JulyComments Comments Aug Sep ford 12 1 101 102 87 ford 168 2 102 87 16 chrysler 44 5 105 74 31 chrysler 54 3 118 166 18 deleted 73 7 147 518 35 deleted 89 9 198 2 68 ferrari 28 5 187 87 18 ferrari 46 1 185 89 82 My problem is how to sum the row without the columns in the middle which cannot be moved. And how to search and sum row by row without using VBA. thanks in advance- Hide quoted text - - Show quoted text - thanks Ron Your formula works well for the table above. However, I drew up this table a little hastily. The comments columns actually contain numberical data. So the formula will include those columns, won't it? How do I exclude those columns? Thanks again for your help.- Hide quoted text - - Show quoted text - Hi Ron thanks again. I think my example wasn't as helpful as I had hoped. It's more of an example, the actual workbook I have contains 150 columns of numerical data and there is a group of about 25 columns in the middle which I want to exclude from the total. They have quite longwinded and different column headings so the method you suggested above wouldn't work. To be more specific there are many columns of different quarterly information and many rows of different contracts. Usually I would use a pivot table to summarise this but in this case i need a singe result returned by a formula. I want to sum those rows that do not contain the phrase deleted, and exclude a big middle group of columns that contain irrelevant quarterly data. Is there any way to do this? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum multiple arrays of data according to criteria
On 24 Jul, 18:42, Ron Coderre
wrote: The simplest approach may be to simply flag the columns to be skipped by entering the word "skip" in a row above or below the data..... In this example, I inserted a row above the data and entered "skip" in cells B1, E1 and F1: =SUMPRODUCT(($A$3:$A$10=J2)*($B$1:$H$1<"skip")*$B $3:$H$10) Alternatively, you can specify which columns to skip within the formula. If the list is short.....in this case, I skip Columns 5 and 6: G1: =SUMPRODUCT(($A$2:$A$9<"deleted")*ISNA(MATCH(COLU MN($B$2:$H$9),{5;6},0))*$*B$2:$H$9) or....if the list is varied and long, put the list of column numbers in a range off to the side (I'll use P1:P10) and use this formula: G1: =SUMPRODUCT(($A$2:$A$9<"deleted")*ISNA(MATCH(COLU MN($B$2:$H$9),$P$1:$P$10,*0))*$B$2:$H$9) Am I helping yet? *********** Regards, Ron XL2003, WinXP " wrote: On 24 Jul, 18:04, Ron Coderre wrote: Hmmm....I guessed the other way...that the Comments columns would contain text. See if these ARRAY FORMULAS (that skip the comments column completely) help: If there may be text anyplace in the sum range G1: =SUMPRODUCT(($A$2:$A$9=J1)*($B$1:$H$1<"Comments") *IF(ISNUMBER($B$2:$H$9),$*-B$2:$H$9)) or =SUMPRODUCT(($A$2:$A$9<"deleted")*($B$1:$H$1<"Co mments")*IF(ISNUMBER($B$2*-:$H$9),$B$2:$H$9)) OR.....if there will NEVER be text in the sum range, then try thes REGULAR FORMULAS (committed with just Enter): G1: =SUMPRODUCT(($A$2:$A$9=J1)*($B$1:$H$1<"Comments") *$B$2:$H$9) or =SUMPRODUCT(($A$2:$A$9<"deleted")*($B$1:$H$1<"Co mments")*$B$2:$H$9) Does that help? *********** Regards, Ron XL2003, WinXP " wrote: On 24 Jul, 17:38, Ron Coderre wrote: With your posted table of information in cells A1:H9 This formula returns the total values for Ford F1: Ford G1: =SUMPRODUCT(($A$2:$A$9=J1)*$B$2:$H$9) OR...if you want the total of all rows that do not begin with "delted" G1: =SUMPRODUCT(($A$2:$A$9<"deleted")*$B$2:$H$9) Is that something you can work with? (Post back if you have more questions.) *********** Regards, Ron XL2003, WinXP " wrote: I have had a good look through the posts but haven't found a problem quite like this, though I am sure it has come up before. Are you able to obtain a single result for the following problem. For each car manufacturer, sum all the months of data, excluding the comment columns, for any row that does not contain the phrase deleted. But I don't want to use a pivot table or VBA. May June JulyComments Comments Aug Sep ford 12 1 101 102 87 ford 168 2 102 87 16 chrysler 44 5 105 74 31 chrysler 54 3 118 166 18 deleted 73 7 147 518 35 deleted 89 9 198 2 68 ferrari 28 5 187 87 18 ferrari 46 1 185 89 82 My problem is how to sum the row without the columns in the middle which cannot be moved. And how to search and sum row by row without using VBA. thanks in advance- Hide quoted text - - Show quoted text - thanks Ron Your formula works well for the table above. However, I drew up this table a little hastily. The comments columns actually contain numberical data. So the formula will include those columns, won't it? How do I exclude those columns? Thanks again for your help.- Hide quoted text - - Show quoted text - Hi Ron thanks again. I think my example wasn't as helpful as I had hoped. It's more of an example, the actual workbook I have contains 150 columns of numerical data and there is a group of about 25 columns in the middle which I want to exclude from the total. They have quite longwinded and different column headings so the method you suggested above wouldn't work. To be more specific there are many columns of different quarterly information and many rows of different contracts. Usually I would use a pivot table to summarise this but in this case i need a singe result returned by a formula. I want to sum those rows that do not contain the phrase deleted, and exclude a big middle group of columns that contain irrelevant quarterly data. Is there any way to do this?- Hide quoted text - - Show quoted text - that's great Ron. Thanks for your help with this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple data validation criteria | Excel Discussion (Misc queries) | |||
Calculating Mode for multiple arrays based on criteria in another | Excel Worksheet Functions | |||
finding data with multiple criteria | Excel Discussion (Misc queries) | |||
Product of 2 arrays based on criteria | Excel Discussion (Misc queries) | |||
Sorting Data by multiple criteria | Excel Discussion (Misc queries) |