formula help

Kiwi-123
Kiwi-123 used Ask the Experts™
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

Many thanks for your help.
new.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
You may use DCount, DMin, DMax, DAverage function...
Please refer to the attachment
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
byundtMechanical 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

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