Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUM 2 Conditions
Im trying to create a formula that will sum the values of one colum based on the values of two other colums Colum A is Date Colum C is Name Colum E is a Time value Im trying sum the times values based on criteria from colums a and c I want to sum the time Of a person on a certain date for an entire colums length I have used the sumif forumla but that only allows 1 type of criteria to be matched and i need to match it based on two types of criteria. Here is a sample sumif command that works but only gives me the sum of all values of a day =SUMIF('Incoming Calls'!A:A,B5,'Incoming Calls'!E:E) or vice versa =SUMIF('Incoming Calls'!C:C,A4,'Incoming Calls'!E:E) I need a sum of colum E based on these two criteria. Any suggestions? -- mav3n ------------------------------------------------------------------------ mav3n's Profile: http://www.excelforum.com/member.php...o&userid=26062 View this thread: http://www.excelforum.com/showthread...hreadid=394015 |
#2
|
|||
|
|||
here is a sum statement with if and AND in it that i tried return #value Code: -------------------- =SUM(IF(AND('Incoming Calls'!A2:A94=Summary!B5,'Incoming Calls'!C2:C94=Summary!A4),'Incoming Calls'!E2:E94,"")) -------------------- -- mav3n ------------------------------------------------------------------------ mav3n's Profile: http://www.excelforum.com/member.php...o&userid=26062 View this thread: http://www.excelforum.com/showthread...hreadid=394015 |
#3
|
|||
|
|||
=SUMPRODUCT(('Incoming Calls'!$A$2:$A$100=B5)+0,('Incoming
Calls'!$C$2:$C$100=A4)+0,'Incoming Calls'!$E$2:$E$100=B5) Custom format the formula cell as: [h]:mm. Note that this type of formulas do not admit whole columns like E:E as range arguments. mav3n wrote: Im trying to create a formula that will sum the values of one colum based on the values of two other colums Colum A is Date Colum C is Name Colum E is a Time value Im trying sum the times values based on criteria from colums a and c I want to sum the time Of a person on a certain date for an entire colums length I have used the sumif forumla but that only allows 1 type of criteria to be matched and i need to match it based on two types of criteria. Here is a sample sumif command that works but only gives me the sum of all values of a day =SUMIF('Incoming Calls'!A:A,B5,'Incoming Calls'!E:E) or vice versa =SUMIF('Incoming Calls'!C:C,A4,'Incoming Calls'!E:E) I need a sum of colum E based on these two criteria. Any suggestions? -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
#4
|
|||
|
|||
Make that:
=SUMPRODUCT(('Incoming Calls'!$A$2:$A$100=B5)+0,('Incoming Calls'!$C$2:$C$100=A4)+0,'Incoming Calls'!$E$2:$E$100) Aladin Akyurek wrote: =SUMPRODUCT(('Incoming Calls'!$A$2:$A$100=B5)+0,('Incoming Calls'!$C$2:$C$100=A4)+0,'Incoming Calls'!$E$2:$E$100=B5) Custom format the formula cell as: [h]:mm. Note that this type of formulas do not admit whole columns like E:E as range arguments. mav3n wrote: Im trying to create a formula that will sum the values of one colum based on the values of two other colums Colum A is Date Colum C is Name Colum E is a Time value Im trying sum the times values based on criteria from colums a and c I want to sum the time Of a person on a certain date for an entire colums length I have used the sumif forumla but that only allows 1 type of criteria to be matched and i need to match it based on two types of criteria. Here is a sample sumif command that works but only gives me the sum of all values of a day =SUMIF('Incoming Calls'!A:A,B5,'Incoming Calls'!E:E) or vice versa =SUMIF('Incoming Calls'!C:C,A4,'Incoming Calls'!E:E) I need a sum of colum E based on these two criteria. Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
More than 3 Conditional Formatting Conditions | Excel Worksheet Functions | |||
Add cells from a range based on 2 conditions from 2 other ranges | Excel Worksheet Functions | |||
Conditional formatting: I have five conditions, how to do this? | Excel Discussion (Misc queries) | |||
How do I count number of cels the matches 2 conditions ? | Excel Worksheet Functions | |||
How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions |