MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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