Counting amount incorrect within a date range.

Hi there

I would like some help from someone on this subject if possible. I am running a stats report in a workbook where each member of staff have their own running totals of how much work they do and how much of that work is incorrect (per week). I have worked out that if I use the following:-

=SUMPRODUCT(('Barry Brooks'!$A$1:$A$2000>=$B$95)*('Barry Brooks'!$A$1:$A$2000<=$C$95))

I get the total amount of work. To explain the above, it returns the amount of times a date appears between two dates i.e B95 is week commencing date and C95 is weekending; therefore giving the total amount of work in that period.

What I am struggling with is that I need a formula to look at the above date range and then look at another column that has either a ‘Y’ or ‘N’ (for yes or no) and for the formula to count how many N’s there are (thus how many in that given range there were)

It appears to me quite a small ask for Excel but I can’t seem to get it to work.

I would really appreciate anyone’s input on this.

Many thanks in advance.

Neil Shaw

Nothing wrong with zbor's offering but I would say that it makes more sense to use either * or -- ... occasionally useful to mix the two but sometimes more confusing

=SUMPRODUCT(('Barry Brooks'!$A$1:$A$2000>=$B$95)*('Barry Brooks'!$A$1:$A$2000<=$C$95)*('Barry Brooks'!$C$1:$C$2000="No"))

or

=SUMPRODUCT(--('Barry Brooks'!$A$1:$A$2000>=$B$95),--('Barry Brooks'!$A$1:$A$2000<=$C$95),--('Barry Brooks'!$C$1:$C$2000="No"))

the latter method is regarded as slightly more efficient but does have it's own limitations - for more info on Sumproduct see link to Bob Phillips' white paper on the function in my signature.