If condition whilst using dates

**OnlyFun** · 11-01-2007, 02:09 AM

Hi,

I have prepared an excel sheet to derive quality stats for my team.

My requirement is:

As per the dates pull up the requisite records (Audits passed out of total audits done) and compute the formulae. I have used the "If" condition, but to no avail.

The formula I used is:

=IF('XYZ'!$B$1:$B$59<=39388,COUNTIF('XYZ'!$E$1:$E$59,"Pass")) where XYZ is the name of the person

Here 39388 indicates November 2, 2007

Please can someone help me?

**Bjornar** · 11-01-2007, 03:15 AM

Can you post an example sheet of your data and what result you want ?
Put the sheet in a .zip file and attach to your reply

**VBA Noob** · 11-01-2007, 03:17 AM

Maybe

=SUMPRODUCT(--(TEXT(XYZ!$B$1:$B$59,"dd/mm/yy")="02/11/07")*(XYZ!$E$1:$E$59="Pass"))

VBA Noob

**OnlyFun** · 11-01-2007, 02:20 PM

Originally Posted by Bjornar

Can you post an example sheet of your data and what result you want ?
Put the sheet in a .zip file and attach to your reply

Have attached the same. Thank you.

**OnlyFun** · 11-01-2007, 02:27 PM

Originally Posted by VBA Noob

Maybe

VBA Noob

Thank you very much for your help.

**OnlyFun** · 11-01-2007, 03:03 PM

Originally Posted by VBA Noob

Maybe

VBA Noob

Thanks for this.

What if I wish compute quality as per the indicated date range, say between 05/11 to 09/11 as mentioned in my sheet above.

Thanks again.

**VBA Noob** · 11-01-2007, 03:08 PM

See if this helps

=SUMPRODUCT(--(TEXT(XYZ!$B$1:$B$59,"dd/mm/yy")>="05/11/07")*(TEXT(XYZ!$B$1:$B$59,"dd/mm/yy")<="09/11/07")*(XYZ!$E$1:$E$59="Pass"))

VBA Noob

**OnlyFun** · 11-01-2007, 03:41 PM

Originally Posted by VBA Noob

See if this helps

VBA Noob

Thank you so much. That worked.

Also, if I wish to count the pass out of (total) as per the date range specified, how do I do it? Earlier as per my attached sheet I had used CountA, but without specifying the date range.

In case I want to change the formulaes across the cells as per the name of the persons (actual name instead of XYZ as per the name of different employees) as mentioned in the sheet quickly, is there a faster way than the current manual method which I am resorting to?

Many thanks for all your help.