Thanks for the prompt reply. Although the column was formatted correctly,
beyond the visible screen a single column heading had pasted there when I did
a sort. Thanks to your search formula I removed the one cell of text and the
formula now works. I also appreciate the simplified syntax! Now I am about to
try the solution to the h:m formatting.
--
Excelsolutions4U


"Peo Sjoblom" wrote:

The values errors derive from text being calculated so before anything else
you need to make sure the data is not text.
Many times when importing what looks like numbers from mainframes and other
programs Excel sees them as text and regardless if you change the format in
the cell after the import is done it will stay that way. Note that in your
case the text error comes from column O

Test the values with


=ISTEXT('[Kronos Daily Labor Report]2'!$O$15)

copy down

if you get TRUE anywhere it is text

If so post back

Also the formula can be simplified to

=SUMPRODUCT(('[Kronos Daily Labor
Report]2'!$O$15:$O$365)*('[Kronos Daily Labor
Report]2'!$I$15:$I$365={100,130}))

That will not stop the values errors though

Try to select the import in column O, then do datatext to column and click
finish
and see if the value error disappears


Finally format the result as [hh]:mm:ss, that way it won't spill over to
days etc





--


Regards,


Peo Sjoblom

"Karen" wrote in message
...
I have to use an hours:minutes column from a Kronos payroll report(exported
to Excel) in the following formula:=SUMPRODUCT(('[Kronos Daily Labor
Report]2'!$O$15:$O$365)*('[Kronos Daily Labor
Report]2'!$I$15:$I$365=100)+('[Kronos Daily Labor Report
.xls]2'!$I$15:$I$365=130))

(Sum column O if the position code is 100 or 130)
This formula is on an external spreadsheet.

I am getting a value error and my first problem is to determine the
formatting in the h:mm column coming from the payroll report. I have
followed
previous threads, copied blank cells and pasted the formatting, etc. all
to
no avail. I did something to make it work yesterday, but can't do it
again.
Converting this column will have to be part of a macro.

Second problem I have is when the h:mm are added, the formatting is to be
hours:minutes without changing to days after reaching the 23:59:59 amount.
Will this formatting be on the external spreadsheet containing the formula
or can I fix it all on the payroll sheet with the macro?

If someone will look at these sheets, I can email them. Although I am
using
Vista and Office 2007, the user will have XP and Office 2003. Thank You!
Excelsolutions4U