| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum data if 3 criteria are met
I'm trying to come up with a formula that will let me sum data in one column
if three criteria are met in the other columns. Here is sample data: A2:A65535 = Dates (looking for all items that = 3/15/2008) C2:C65535 = Text (looking for all items that equal = "eChannel") O2:O65535 = Text (looking for all items that equal = "Direct Expense") AG2:AG65535 = Numbers (I want to sum all numbers if all three criteria above are met for a particular row) So, I want to sum column AG for all rows that have 3/15/2008 in column A, "echannel" in column C, and "Direct expense" in column O. I've tried Ctrl-Shift-Enter formulas but I must not have them correct. Also I've tried SUMPRODUCT formulas but here again I must be doing something wrong. Thanks in advance for any help. -- rdbjr99 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum data if 3 criteria are met
Your date will be a serial number (39522). =SUMPRODUCT(--(A2:A65535=39522),--(C2:C65535="eChannel"),--(O2:O65535="Direct Expense"),AG2:AG65535) If criteria to be met will reside in cells, then you can replace with the cell references. =SUMPRODUCT(--(A2:A65535=A1),--(C2:C65535=C1),--(O2:O65535=O1),AG2:AG65535) HTH, Paul -- "rdbjr99" wrote in message ... I'm trying to come up with a formula that will let me sum data in one column if three criteria are met in the other columns. Here is sample data: A2:A65535 = Dates (looking for all items that = 3/15/2008) C2:C65535 = Text (looking for all items that equal = "eChannel") O2:O65535 = Text (looking for all items that equal = "Direct Expense") AG2:AG65535 = Numbers (I want to sum all numbers if all three criteria above are met for a particular row) So, I want to sum column AG for all rows that have 3/15/2008 in column A, "echannel" in column C, and "Direct expense" in column O. I've tried Ctrl-Shift-Enter formulas but I must not have them correct. Also I've tried SUMPRODUCT formulas but here again I must be doing something wrong. Thanks in advance for any help. -- rdbjr99 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum data if 3 criteria are met
Paul: Thanks but when I enter the formula you provided I get #N/A returned.
Does it make a difference that the data is in one spreadsheet and the formula is in another? For example I am trying to sum the data in the Mar. Scorecard worksheet and the data is in the FCByAccount worksheet. Here is what my formula looks like: =SUMPRODUCT(--(FCbyAccount!A2:A65535=39522),--(FCbyAccount!C2:C65535="eChannel"),--(FCbyAccount!O2:O65535="Direct Expense"),FCbyAccount!AG2:AG65535) -- rdbjr99 "PCLIVE" wrote: Your date will be a serial number (39522). =SUMPRODUCT(--(A2:A65535=39522),--(C2:C65535="eChannel"),--(O2:O65535="Direct Expense"),AG2:AG65535) If criteria to be met will reside in cells, then you can replace with the cell references. =SUMPRODUCT(--(A2:A65535=A1),--(C2:C65535=C1),--(O2:O65535=O1),AG2:AG65535) HTH, Paul -- "rdbjr99" wrote in message ... I'm trying to come up with a formula that will let me sum data in one column if three criteria are met in the other columns. Here is sample data: A2:A65535 = Dates (looking for all items that = 3/15/2008) C2:C65535 = Text (looking for all items that equal = "eChannel") O2:O65535 = Text (looking for all items that equal = "Direct Expense") AG2:AG65535 = Numbers (I want to sum all numbers if all three criteria above are met for a particular row) So, I want to sum column AG for all rows that have 3/15/2008 in column A, "echannel" in column C, and "Direct expense" in column O. I've tried Ctrl-Shift-Enter formulas but I must not have them correct. Also I've tried SUMPRODUCT formulas but here again I must be doing something wrong. Thanks in advance for any help. -- rdbjr99 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum data if 3 criteria are met
To avoid converting the date separately to its serial number, you could
change =SUMPRODUCT(--(A2:A65535=DATE(2008,3,15)),--(C2:C65535="eChannel"),--(O2:O65535="Direct Expense"),AG2:AG65535) to =SUMPRODUCT(--(A2:A65535=39522),--(C2:C65535="eChannel"),--(O2:O65535="Direct Expense"),AG2:AG65535) -- David Biddulph "PCLIVE" wrote in message ... Your date will be a serial number (39522). =SUMPRODUCT(--(A2:A65535=39522),--(C2:C65535="eChannel"),--(O2:O65535="Direct Expense"),AG2:AG65535) If criteria to be met will reside in cells, then you can replace with the cell references. =SUMPRODUCT(--(A2:A65535=A1),--(C2:C65535=C1),--(O2:O65535=O1),AG2:AG65535) HTH, Paul -- "rdbjr99" wrote in message ... I'm trying to come up with a formula that will let me sum data in one column if three criteria are met in the other columns. Here is sample data: A2:A65535 = Dates (looking for all items that = 3/15/2008) C2:C65535 = Text (looking for all items that equal = "eChannel") O2:O65535 = Text (looking for all items that equal = "Direct Expense") AG2:AG65535 = Numbers (I want to sum all numbers if all three criteria above are met for a particular row) So, I want to sum column AG for all rows that have 3/15/2008 in column A, "echannel" in column C, and "Direct expense" in column O. I've tried Ctrl-Shift-Enter formulas but I must not have them correct. Also I've tried SUMPRODUCT formulas but here again I must be doing something wrong. Thanks in advance for any help. -- rdbjr99 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum data if 3 criteria are met
That should be fine. However, if you have any errors in your data (ex.
#DIV/0!, #N/A, etc.), then your formula will also result in an error. Regards, Paul -- "rdbjr99" wrote in message ... Paul: Thanks but when I enter the formula you provided I get #N/A returned. Does it make a difference that the data is in one spreadsheet and the formula is in another? For example I am trying to sum the data in the Mar. Scorecard worksheet and the data is in the FCByAccount worksheet. Here is what my formula looks like: =SUMPRODUCT(--(FCbyAccount!A2:A65535=39522),--(FCbyAccount!C2:C65535="eChannel"),--(FCbyAccount!O2:O65535="Direct Expense"),FCbyAccount!AG2:AG65535) -- rdbjr99 "PCLIVE" wrote: Your date will be a serial number (39522). =SUMPRODUCT(--(A2:A65535=39522),--(C2:C65535="eChannel"),--(O2:O65535="Direct Expense"),AG2:AG65535) If criteria to be met will reside in cells, then you can replace with the cell references. =SUMPRODUCT(--(A2:A65535=A1),--(C2:C65535=C1),--(O2:O65535=O1),AG2:AG65535) HTH, Paul -- "rdbjr99" wrote in message ... I'm trying to come up with a formula that will let me sum data in one column if three criteria are met in the other columns. Here is sample data: A2:A65535 = Dates (looking for all items that = 3/15/2008) C2:C65535 = Text (looking for all items that equal = "eChannel") O2:O65535 = Text (looking for all items that equal = "Direct Expense") AG2:AG65535 = Numbers (I want to sum all numbers if all three criteria above are met for a particular row) So, I want to sum column AG for all rows that have 3/15/2008 in column A, "echannel" in column C, and "Direct expense" in column O. I've tried Ctrl-Shift-Enter formulas but I must not have them correct. Also I've tried SUMPRODUCT formulas but here again I must be doing something wrong. Thanks in advance for any help. -- rdbjr99 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| looking up data in a table using 2 criteria | Excel Worksheet Functions | |||
| SUM data if meets criteria | Charts and Charting in Excel | |||
| data entry criteria | Excel Worksheet Functions | |||
| arrange data by criteria | New Users to Excel | |||
| under certain criteria copy data. | Excel Worksheet Functions | |||
Linear Mode
