formula help

Kiwi-123 used Ask the Experts™
Please can you help me with the formula's on sheet 2 of the attached workbook.

I'd like the formula's to count the range on sheet 1, but based upon the dates inputted in D3:D4 of sheet 2

Many thanks for your help.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You may use DCount, DMin, DMax, DAverage function...
Please refer to the attachment
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
For the Number in cell D7, you might try: =SUMPRODUCT((Sheet1!B$5:B$103>=D$3)*(Sheet1!B$5:B$103<=D$4)*SUBTOTAL(2,OFFSET(Sheet1!D$5,ROW(Sheet1!D$5:D$103)-ROW(Sheet1!D$5),0)),Sheet1!D$5:D$103)

And for the Count of Yes in cell D8, you might try: =SUMPRODUCT((Sheet1!B$5:B$103>=D$3)*(Sheet1!B$5:B$103<=D$4)*(Sheet1!C$5:C$103="Y")*SUBTOTAL(2,OFFSET(Sheet1!D$5,ROW(Sheet1!D$5:D$103)-ROW(Sheet1!D$5),0)))

For the Minimum, Maximum and Average numbers in cell D12:D14, use the array-entered formulas:



To array-enter a formula:
1) Click in the formula bar
2) Hold the Control and Shift keys down
3) Hit Enter
4) Release all three keys
Excel should respond by adding curly braces { } surrounding the formula
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

For cell D8, if you want the number from Sheet1 column D where column C is Y and column B is between the dates, then try:

If these formulas are not returning what you think are the correct answers, please post the correct answers so we can devise a formula to return them. Note that the dates in your sample workbook didn't result in a useful test. So I changed the dates to those that did.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial