Help! ... & the joys of SUMPRODUCT

[BunnyDelux]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I'm new to the joys of SUMPRODUCT but having now discovered it am hoping that its genius will make my life easier.. and am therefore in the process of constructing a spreadsheet which gives me an overview of my payroll.. But have unfortunately hit a wall in my understanding.

So far I've got two sheets; one where payroll info is entered and a second which summarises this by staff name and gives me a month by month running total of staff wages that are owed.

So far I've used SUMPRODUCT to sum a range of cells if certain other cells match the following criteria:

B15:B41="Name"
I15:I41="Y"
E15:E41="Jan"
F15:F41="'09"

The range is J15:J41

So I've developed this which works:

=SUMPRODUCT(--(PAYROLL!B15:B41="Shae Mutu"),--(PAYROLL!I15:I41="Y"),--(PAYROLL!E15:E41="Jan"),--(PAYROLL!F15:F41="'09")--(PAYROLL!J15:J41))

But now I need to add a new criteria into the mix which in laymans terms needs to express whether the Y (that will be entered into 115:I41) was entered before or after a certain date, ie. whether it was entered in Jan, Feb, Mar etc.

I hope this makes some kind of sense.. & just wondered whether the date of this "Y" data entry could be somehow tracked by excel and if so how would I include this as a criteria in my SUMPRODUCT formula.

Can anybody help?

Thanks.. BD x

 

[JE McGimpsey]

I hope this makes some kind of sense.. & just wondered whether the date of
this "Y" data entry could be somehow tracked by excel and if so how would I
include this as a criteria in my SUMPRODUCT formula.

XL doesn't store information about when an entry was made, so in order
to do what I THINK you're looking for, you'd need to enter the date that
you enter Y in a separate column, and add that column to your SUMPRODUCT.