Countif between dates help

[Lensmeister]

Hi all,

I am hoping someone can help me with a formula that will populate a report.

We have 4 week reporting blocks.

The dates are held in the named ranges:
wk1Start, wk1End, wk2Start, wk2End.


I need to do a formula that works thus:

If a row is between wk1Start and wk1End countif(Column E on sheet called Current Period matches the Report sheet cell B2),"")

I have added a sample if this makes it clearer.

Thank in advance

[TMS]

If using Excel 2003:

Cell D7: =SUMPRODUCT(--(CurrentPeriod!$B$1:$B$31=Report!$C7),--(CurrentPeriod!$A$1:$A$31>=INDIRECT(LEFT(D$6,3)&"Start")),--(CurrentPeriod!$A$1:$A$31<INDIRECT(LEFT(D$6,3)&"End")))

Drg down and across.

If using Excel 2007, use COUNTIFS


Regards

[Lensmeister]

Thanks for this.

I have tried it and it worked in the testip file I uploaded. But when I adapted it for my own sheet it didn't work

I have reattached it to see why it never worked.

Thanks again.

[TMS]

OK, I've reworked the formula to reflect the restructured workbook. See attached.

For future reference, you will help yourself, and others, if you apply the formula to the original test workbook and restructure the test workbook to match the production workbook. In that way, you are more likely to be able to copy the formulae from the test environment to the production environment.

You'll save yourself and everyone involved time and effort.

Regards, TMS