SUMIF Question

[carlot]

Hi. I am trying to use the sumif function to total all
sales in the morning vs the afternoon.

My time data looks like "2004 12:00:00 PM" and is coming
from a different application.

When I try to use the sumif the time "< 2004 12:00:00 PM"
returns "0".

Thanks in advance.

 

[Vasant Nanavati]

"2004 12:00:00 PM" is not a time; it is a string. Try something like:

"<"&TIMEVALUE("12:00:00 PM")

or:

"<.5"

since .5 of a day corresponds to 12 noon and Excel tracks time in fractions
of a day.

 

[Frank Kabel]

Hi
I suppose your data '2004 12:00:00 PM' is a text entry? If yes
SUMPRODUCT won't work. If it is a date/time value in Excel you may use
the following (column A stores the date/time and column B the values to
be summed)
=SUMPRODUCT(--((A1:A1000-INT(A1:A1000))<TIME(12,0,0)),B1:B1000)

 

[Harald Staff]

I believe this -year(?) & time- will be treated as text, it makes no sense as a date or as
time. See first if data 1004 12:00:00 PM in a cell will return 1 to the function.