Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multple criteria dilemma
Hi
I really hope someone out there can help with this problem. I need to calculate a total for rows that meet one or more criteria, however what complicates matters for me is that the number of criteria changes, sometimes it will be one, sometimes four, sometimes three etc etc. I need to accomplish this without resorting to VBA. The worksheet that contains the criteria has the following format, with the criteria in row 2 A B C D E 1 Year Account Manager Client Product Revenue Type 2 2005 Brendan Gannon FNB Businees Objects Product At times I will want a total for rows that meet less than the five criteria for example A B C D E 1 Year Account Manager Client Product Revenue Type 2 Brendan Gannon FNB Product Sometimes there will be only one criteria, for example A B C D E 1 Year Account Manager Client Product Revenue Type 2 Businees Objects The data portion resides below the criteria range in row 5 through to 100 and the column that needs to be summed is Column F, row 5 through to 100 Any help in this regard will be much appreciated. Kind Regards - Grant |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multple criteria dilemma
Hi Grant
I'm not sure I fully understand what you are looking for, but maybe in F2 you could enter =COUNTA(A2:E2) and copy down. This will give a count of the number of columns with data entered. If you then want to know how many rows have 3 items entered =COUNTIF(F2:F100,3) -- Regards Roger Govier "Grant Reid" wrote in message ... Hi I really hope someone out there can help with this problem. I need to calculate a total for rows that meet one or more criteria, however what complicates matters for me is that the number of criteria changes, sometimes it will be one, sometimes four, sometimes three etc etc. I need to accomplish this without resorting to VBA. The worksheet that contains the criteria has the following format, with the criteria in row 2 A B C D E 1 Year Account Manager Client Product Revenue Type 2 2005 Brendan Gannon FNB Businees Objects Product At times I will want a total for rows that meet less than the five criteria for example A B C D E 1 Year Account Manager Client Product Revenue Type 2 Brendan Gannon FNB Product Sometimes there will be only one criteria, for example A B C D E 1 Year Account Manager Client Product Revenue Type 2 Businees Objects The data portion resides below the criteria range in row 5 through to 100 and the column that needs to be summed is Column F, row 5 through to 100 Any help in this regard will be much appreciated. Kind Regards - Grant |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multple criteria dilemma
Hi Roger
Many thanks for your response. After reading my original question, I realised I had over complicated matters. What I'm actually trying to accomplish is this..... I have data in A5:F100, I need to sum the numeric data in F5:F100 that meets criteria I have A2:E2. Sometimes I'll need sum the data that meets all 5 criteria, sometimes I'll need to sum data that meets just one, sometimes three etc etc So sometimes it will look like this A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx xxxx xxxx xxxx xxxx sometimes it will look like this A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx xxxx xxxx sometimes like this A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx etc etc. So my problem is how to sum the data in F5:F100 when the number and position of criteria vary. Kind Regards - Grant |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multple criteria dilemma
Hi Grant
The take a look at the Sumproduct function =SUMPRODUCT(--($A$2:$A$100="criteria1"),--($B$2:$B$100="criteria2"),$F2:$F100) Keeping F2:F100 constant, insert or delete as ny --($XX2:$XX100="criteria") as you wish, (where XX equals your column letter) Change the range to suit your needs, but do ensure that each range used is of equal length. -- Regards Roger Govier "Grant Reid" wrote in message ... Hi Roger Many thanks for your response. After reading my original question, I realised I had over complicated matters. What I'm actually trying to accomplish is this..... I have data in A5:F100, I need to sum the numeric data in F5:F100 that meets criteria I have A2:E2. Sometimes I'll need sum the data that meets all 5 criteria, sometimes I'll need to sum data that meets just one, sometimes three etc etc So sometimes it will look like this A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx xxxx xxxx xxxx xxxx sometimes it will look like this A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx xxxx xxxx sometimes like this A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx etc etc. So my problem is how to sum the data in F5:F100 when the number and position of criteria vary. Kind Regards - Grant |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multple criteria dilemma
Another option is to create a pivot table from the data, and add fields
to the page area. Use these fields to filter the results, or to show results for all records. There are examples and links he http://www.contextures.com/xlPivot01.html Grant Reid wrote: Hi I really hope someone out there can help with this problem. I need to calculate a total for rows that meet one or more criteria, however what complicates matters for me is that the number of criteria changes, sometimes it will be one, sometimes four, sometimes three etc etc. I need to accomplish this without resorting to VBA. The worksheet that contains the criteria has the following format, with the criteria in row 2 A B C D E 1 Year Account Manager Client Product Revenue Type 2 2005 Brendan Gannon FNB Businees Objects Product At times I will want a total for rows that meet less than the five criteria for example A B C D E 1 Year Account Manager Client Product Revenue Type 2 Brendan Gannon FNB Product Sometimes there will be only one criteria, for example A B C D E 1 Year Account Manager Client Product Revenue Type 2 Businees Objects The data portion resides below the criteria range in row 5 through to 100 and the column that needs to be summed is Column F, row 5 through to 100 Any help in this regard will be much appreciated. Kind Regards - Grant -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multple criteria dilemma
The DSUM function is good for rapidly summing large sets of data with
multiple criteria. The way you described your criteria (with headings) in A1:E2 is exactly what DSUM requires, and it's fine if the number of criteria (A2:E2 cells with values) varies from one time to the next. Check the Excel help for DSUM. Hope this helps, Hutch "Grant Reid" wrote: Hi Roger Many thanks for your response. After reading my original question, I realised I had over complicated matters. What I'm actually trying to accomplish is this..... I have data in A5:F100, I need to sum the numeric data in F5:F100 that meets criteria I have A2:E2. Sometimes I'll need sum the data that meets all 5 criteria, sometimes I'll need to sum data that meets just one, sometimes three etc etc So sometimes it will look like this A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx xxxx xxxx xxxx xxxx sometimes it will look like this A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx xxxx xxxx sometimes like this A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx etc etc. So my problem is how to sum the data in F5:F100 when the number and position of criteria vary. Kind Regards - Grant |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multple criteria dilemma
Hi
Many Thanks to all who responded. Your help is much appreciated. Unfortunately the goal posts have been shifted and I now have to rethink my approach to this problem. Once again, any help would be much appreciated. I'm now required to embed this spreadsheet into another product (Crystal Xcelsius - a dashboarding product that sits on top of Excel) and therefore things become much more rigid. I cannot use VBA in the spreadsheet, I cannot use any the menu options neither can I make use of functionality such as pivot tables. I am restricted to entering/deleting/modifying data in 5 cells and producing 12 different results by means of functions. I now have my data in A5:G100 and need to sum the numeric data in G5:G100 that meets criteria I have A2:E2. Sometimes I'll need sum the data that meets all 5 criteria, sometimes I'll need to sum data that meets just one, sometimes three etc etc. My data now has an additional column, this contains the month. The layout is like this (row 5 through row 100); A B C D E F G Row 5 Year Acc Clnt Prod Rev Month Amount My critera will sometimes look like this A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx xxxx xxxx xxxx xxxx sometimes it will look like this A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx xxxx xxxx sometimes like this A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx etc etc. So I still have the same problem, how to sum the data in F5:F100 when the number and position of criteria vary and if I had to produce this result in one cell, DSUM would be the ideal solution. But now I have to produce a result for each month of the year, based on the same criteria entered in A1:E2. I somehow need to append this additional "Month" criteria to the original criteria and produce 12 seperate results in 12 different cells. I've attempted to create 12 different criteria areas, one for each month, adding the month to my criteria for each and "Paste Linking" to my original criteria in A1:E2. This works fine as long as all five criteria are entered in A1:E2. As soon as I remove one of the criteria, the corresponding "Paste Linked" criteria shows 0 and the expected results are not returned. Once again, any help would be much appreciated. Kind Regards - Grant |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multple criteria dilemma
Hi Grant
Sumproduct should still provide your solution. Take a lock at Bob Phillips site for more help on this function http://xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Roger Govier "Grant Reid" wrote in message ... Hi Many Thanks to all who responded. Your help is much appreciated. Unfortunately the goal posts have been shifted and I now have to rethink my approach to this problem. Once again, any help would be much appreciated. I'm now required to embed this spreadsheet into another product (Crystal Xcelsius - a dashboarding product that sits on top of Excel) and therefore things become much more rigid. I cannot use VBA in the spreadsheet, I cannot use any the menu options neither can I make use of functionality such as pivot tables. I am restricted to entering/deleting/modifying data in 5 cells and producing 12 different results by means of functions. I now have my data in A5:G100 and need to sum the numeric data in G5:G100 that meets criteria I have A2:E2. Sometimes I'll need sum the data that meets all 5 criteria, sometimes I'll need to sum data that meets just one, sometimes three etc etc. My data now has an additional column, this contains the month. The layout is like this (row 5 through row 100); A B C D E F G Row 5 Year Acc Clnt Prod Rev Month Amount My critera will sometimes look like this A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx xxxx xxxx xxxx xxxx sometimes it will look like this A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx xxxx xxxx sometimes like this A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx etc etc. So I still have the same problem, how to sum the data in F5:F100 when the number and position of criteria vary and if I had to produce this result in one cell, DSUM would be the ideal solution. But now I have to produce a result for each month of the year, based on the same criteria entered in A1:E2. I somehow need to append this additional "Month" criteria to the original criteria and produce 12 seperate results in 12 different cells. I've attempted to create 12 different criteria areas, one for each month, adding the month to my criteria for each and "Paste Linking" to my original criteria in A1:E2. This works fine as long as all five criteria are entered in A1:E2. As soon as I remove one of the criteria, the corresponding "Paste Linked" criteria shows 0 and the expected results are not returned. Once again, any help would be much appreciated. Kind Regards - Grant |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multple criteria dilemma
Hi
I am still bumping my head with this one. I've had a look at http://xldynamic.com/source/xld.SUMPRODUCT.html as Roger suggested. It certainly seems as if Sumproduct could be the answer, but for the life of me I can't figure how to apply it to what I'm trying to accomplish. Just to summarise again what I'm trying accomplish. I have my data residing in A6:G100 A B C D E F G Row 5 Year Acc Clnt Prod Rev Month Amount I need to sum the numeric data in G5:G100 that meets criteria I have in A2:E2 A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx xxxx xxxx xxxx xxxx but I need to do this 12 times - one result for every month in 12 different cells - assume results go H1:H12. The data in the month column, F6:F100 is numeric, obviously ranging 1 through to 12. To complicate matters even further, sometimes I will have number of criteria permutations in A2:E2. Sometimes I will have all five criteria, sometimes three, sometimes four, somtimes two, sometimes one and even on occasion, none. Below is an example of where I have three criteria A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx xxxx xxxx and one criteria A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx etc etc All of this has to be accomplished without resorting to array formulae, VBA or functionality such as pivot tables. Any further help will be much appreciated Kind Regards - Grant |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multple criteria dilemma
Hi Grant
Compared with the original posting,column G now appears to contain your values to be summed =SUMPRODUCT(--($A$2:$A$100="criteria1"), --($B$2:$B$100="criteria2"), --($C$":$C$100="criteria3), --($D$2:$D$100="criteria4"), --($E$2:$E$100="criteria5"), --($F$2:$F$100=1), $G2:$G100) This would test the case where Month is 1 (F2:F100=1), and that there were criteria in the other 5 columns. Omit the section relating to any one of the columns to exclude that from the result. If you are going to do this for each month, with 1 in H1, 2 in H2 etc, change to --($f$2:$F$100=H1) and copy down. To deal with anything from 1 to 5 criteria, you would need to copy across, with each formula containing an additional criteria, so you would have a matrix of 60 cells with the differing results. -- Regards Roger Govier "Grant Reid" wrote in message ... Hi I am still bumping my head with this one. I've had a look at http://xldynamic.com/source/xld.SUMPRODUCT.html as Roger suggested. It certainly seems as if Sumproduct could be the answer, but for the life of me I can't figure how to apply it to what I'm trying to accomplish. Just to summarise again what I'm trying accomplish. I have my data residing in A6:G100 A B C D E F G Row 5 Year Acc Clnt Prod Rev Month Amount I need to sum the numeric data in G5:G100 that meets criteria I have in A2:E2 A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx xxxx xxxx xxxx xxxx but I need to do this 12 times - one result for every month in 12 different cells - assume results go H1:H12. The data in the month column, F6:F100 is numeric, obviously ranging 1 through to 12. To complicate matters even further, sometimes I will have number of criteria permutations in A2:E2. Sometimes I will have all five criteria, sometimes three, sometimes four, somtimes two, sometimes one and even on occasion, none. Below is an example of where I have three criteria A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx xxxx xxxx and one criteria A B C D E Row 1 Year Acc Clnt Prod Rev Row 2 xxxx etc etc All of this has to be accomplished without resorting to array formulae, VBA or functionality such as pivot tables. Any further help will be much appreciated Kind Regards - Grant |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DCOUNTA Complex Criteria Question | Excel Worksheet Functions | |||
Returning Results Based on Two Criteria | Excel Worksheet Functions | |||
sorting more than 3 keys | Excel Discussion (Misc queries) | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |