calculation int'l calls

[mwvirk]

i want total time if there is a int'l call (number will be more than 10 digits) in cell D range and cell range C contains 501
i mean total for all int'l calls should appear if caller is 501 in cell range C

 

[mikerickson]

Perhaps SUMIF would work for you.

 

[mwvirk]

thnx mikerickson. yes but its quite difficult for me as i'm new in excel.
all i need is to get the total time for int'l calls if caller is 501 in cell range C and if number in cell range D in larger than 10. pls.

 

[mikerickson]

What column has the time that is being totaled?

 

[mwvirk]

B ------------ C -------- D
Call duration Caller Dialled_number
00:00:46 24418751 500
00:00:03 24418751 9700
00:00:00 4
00:00:07 420 9024418751
00:01:06 420 90561262371
00:00:42 420 9024418451

check C if it is 501 and then check D if it is int'l # (normally it is > 10 char) then get take the time from B and give me total somewhere

 

[mikerickson]

You may need to change the 100 rows in this formula to match your situation.

=SUMPRODUCT(--(C1:C100=501),--(D1:D100>999999999),B1:B100)

 

[mwvirk]

this the actual range i'm looking for:

=SUMPRODUCT(--(C3:C13460=501),--(D3:D13460>9999999999),B3:B13460)

and its giving me error:
THE FORMULA IN THIS CELL REFERS TO A RANGE THAT HAS ADDITIONAL NUMBERS ADJACENT TO IT.

 

[ZVI]

...its giving me error:
THE FORMULA IN THIS CELL REFERS TO A RANGE THAT HAS ADDITIONAL NUMBERS ADJACENT TO IT.

Excel found the data above or below the ranges of the formula and just warns about it. It's OK to ignore it. You can click on smart tag and choose Ignore error.
Or into Excel Options on Error Checking tab clear the "Formula omits cells in region" check box to switch off such error checking option.

 

[mwvirk]

thnx guys... its working fine.