compare two columns of data, excel 97 array
Posted by ken thompson on August 05, 2000 7:11 AM
I need to compare two columns of data and get a total count in one cell.
B1:B50 will be mixed with either night or day shift.
C1:C50 will be dates during 4-1-00,5-1-00 or 6-1-00.
I tried to get a total count of night workers during a particular month(april)
using a combination of array, sum(if(B1:B50,"night"
but I'm unable to complete it.
your help would be appreciated,
thank you, ken
Posted by Ivan Moala on August 05, 0100 8:59 AM
Hi Ken
Try this array formula
ArayFormula enter via Ctrl + shift + enter
=SUM((C1:C50<DATEVALUE("1/5/2000"))*(B1:B50="night"))
The one above just looks @ dates less then May
and shift = night
OR
The one below looks at a date between March & May
ie = April
=SUM((C1:C50<DATEVALUE("1/5/2000"))*(C1:C50>DATEVALUE("31/3/2000"))*(B1:B50="night"))
Just change night to day for day shift &
the dates to look @ diff periods.
HTH
Ivan
Posted by Ivan Moala on August 05, 0100 9:01 AM
Hi Ken
Try this array formula
ArayFormula enter via Ctrl + shift + enter
=SUM((C1:C50<DATEVALUE("1/5/2000"))TIMES(B1:B50="night"))
The one above just looks @ dates less then May
and shift = night
OR
The one below looks at a date between March & May
ie = April
=SUM((C1:C50<DATEVALUE("1/5/2000"))TIMES(C1:C50>DATEVALUE("31/3/2000"))*(B1:B50="night"))
Just change night to day for day shift &
the dates to look @ diff periods.
HTH
Ivan
Posted by Ivan Moala on August 05, 0100 9:02 AM
Look in comments box - as the formula didnt come out in the post
