3 level count if formula

[XCESIV]

Good afternoon people.

I have a bit of a weird formula im trying to work out.

From my thoughts its a 3 level count if

First it is looking on Sheet 1 for the staff members name in Column C
Then It is looking for the type of Certificate used for personal leave in Column F.

the 3rd query it needs to count is it must count only for Certs in the staff members current Year of employeement.

this is what i have so far

=SUMPRODUCT(--(Sheet1!$C$5:$C$6000=$B3)*--(Sheet1!$F$5:$F$6000=D$2))
this checks the staff members name and what cert type, but not the date. Im useless with the Sumproduct function

EG of what i am after
Staff member one has had 3 days off as"No Certificate" since he was employeed.

Start date 01/04/07.

The roll over date where all numbers zero again is 01/04/08 and so on each year

they have had 3 sick days this year
Sick Date 1) 01/06/08
Sick Date 2) 09/09/08
Sick Date 3) 10/10/08

These three dates all fall in the last 12 months of there employeement
so the answer should be 3

If todays current date was 01/04/09, then the answer should roll back to zero

Thanks
Warren

[DonkeyOte]

First off let's just take a look at what you have already:

=SUMPRODUCT(--(Sheet1!$C$5:$C$6000=$B3)*--(Sheet1!$F$5:$F$6000=D$2))

In the above you are using double unary (--), this will coerce the Boolean results to Integer ... you also use * which has the same effect... so essentially you are coercing the Booleans twice ... this is obviously less efficient than doing once, so you should use either/or coercion method not both, ie either:

=SUMPRODUCT((Sheet1!$C$5:$C$6000=$B3)*(Sheet1!$F$5:$F$6000=D$2))

or

=SUMPRODUCT(--(Sheet1!$C$5:$C$6000=$B3),--(Sheet1!$F$5:$F$6000=D$2))

The latter is regarded as slightly quicker than the former but each method has benefits over the other - a good (brief) explanation: http://www.decisionmodels.com/optspeedj.htm

To your Q... you don't specify where the dates are listed so for the sake of demo let's assume that these dates are listed in Sheet1 Column G.
Also it's not clear to me if the roll over dates are specific to each employee or are applied universally ... ie employee starts 01/10/07 does this alter date of significance for current year to 01/10/08 or is still 01/04/08 ?
Again for sake of demo I'll assume the latter that is that all employees share the same roll over dates.

If we assume A1 holds Today's date, A1: =TODAY()

Then one way to count on basis of Staff Name, Certificate & Current Year

=SUMPRODUCT(--(Sheet1!$C$5:$C$6000=$B3),--(Sheet1!$F$5:$F$6000=D$2),--(YEAR(Sheet1!$G$5:$G$6000)+(MONTH(Sheet1!$G$5:$G$6000)>3)=YEAR($A$1)+(MONTH($A$1)>3)))

However by the looks of your references I suspect you're building a Matrix of results here... in which case I would suggest you think about using a Pivot Table to do this directly off your source data (sheet1) ... but perhaps add a column to source that flags the Year based on the Date...

eg: Sheet1!H5: =YEAR(G5)+(MONTH(G5)>3)
this will group all dates from 01/04/08 to 31/03/09 into year 2009 for ex...

You can then set up a PT quite easily I suspect with Name Column as Row Field, Year as Page Field, Certificate type as Column Field and Dates as Data Field set to COUNT.