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

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

=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.

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?