my sumif isn't working properly.

[bigdan]

i downloaded my phonebill in excel. not very coherent.

all calls incoming are free. outgoing are free after 7.

i'm not worry about the after 7 part for now.

Col E shows if its incoming or not (otherwise it says Toronto ie destination i'm calling). Col L shows length of call.

I want to add all the lengths of calls that were not incoming.

=sumif(L:L, E:E<>"incoming")
or even
=sumif(L:L, E:E="incoming")

neither is working. how do i tell it to count the stuff in L if E is NOT incoming.

once i figure that out i'd like to further complicate things by adding those that were before 7PM. The times are in col D however its not as simple as that. col D is in the format

06/22/2011 10:10:00 AM

Can one advise?

 

[hih934]

your syntax is a little off. The statement you have as:
=sumif(L:L, E:E="incoming")
should be:
=sumif(E:E,"incoming", L:L)

 

[Marcelo Branco]

Maybe

=SUMIF(E:E,"<>incoming",L:L)

HTH

M.

 

[bigdan]

thanks guys

hmm another prob i'm having w/ col L, i cant figure out if the results im getting from the sumif are in hours or what. the incoming is 33.397, non-incoming is 18.59

when i highlight col L i get 51.98 at the bottom (the taskbar or whatever its called, that automatically sums what you highlight). however if i only highlight the numbers part of col L, ie i ignore the first 6 lines out of which 5 are blank and one is a column header, i get 1200+. why would this be?

 

[bigdan]

i'm using a filter for now. how does one separate 06/22/11 21:11 into two values, date in one cell time in the other?

 

[Marcelo Branco]

Supposing your data-time in A1

Date
=INT(A1)
to get the Integer part
format as Date mm/dd/yyyy

Time
=MOD(A1,1)
to get the decimal part
format as Time

M.

 

[bigdan]

thanks man. that didn't work though.

the original format was: 06/21/2011 9:11:00 PM
the first formula gave me: 06/21/2011 0:00
the 2nd formula gave me: 01/00/1900 21:11

 

[Marcelo Branco]

thanks man. that didn't work though.

the original format was: 06/21/2011 9:11:00 PM
the first formula gave me: 06/21/2011 0:00
the 2nd formula gave me: 01/00/1900 21:11

Yes, i think this is correct
Format the first cell as Date
The second as Time

M.