# formula help

on
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

new.xls
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
You may use DCount, DMin, DMax, DAverage function...
Formula---Database.xls
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
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:
=MIN(IF((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,""))

=MAX(IF((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,""))

=AVERAGE(IF((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,""))

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
newQ27682487.xls
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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:
=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)),Sheet1!D\$5:D\$103)

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